Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Font colour and Switch function

    Hi have following expression in a query

    TextDue: Switch((DateDiff("d",[Date Due],Date()))>=+1,"Overdue",(DateDiff("d",[Date Due],Date()))>=-90,"Due",(DateDiff("d",[Date Due],Date()))<-90,"Not Due",[Date Due] Is Null,"Overdue?")

    I want to set the font colour differently for each text string
    "Not Due " to green
    "Due" to amber
    "Overdue" + "Overdue?" to red + bold
    Any ideas please, not familiar ( yet but am learning slowly ) with VBA code so if anyone can help please, if possible, can you help with the expression above rather than an alternative in VBA code. Although an alternative in VBA code in addition to amending teh above expression would be helpful for my learning
    Thanks
    Keith

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using conditional formatting
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    39
    Thanks healdem, I need to export the query ( or form datasheet view ) as a spreadsheet to others. When I do this the conditional formatting is lost.
    Is there a way to export from access to another file format ( excel ) and keep condiitonal formatting ?


    Quote Originally Posted by healdem View Post
    consider using conditional formatting

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are confusing different issues here
    the query is the query, it extracts data from the db

    you then use a form or report as the front end to do the formatting / presentational issues
    its in the form or the report where you apply "conditional formatting". you can apply similar conditional ofrmatting in Excel if required

    if you are displaying the data extracted by the query in a spreadsheet then use the tools in your spreadsheet. if you are using Access's 'datasheet' format then you are on your own as far as Im concerned. I don't allow users to to see data as a datasheet as they then want to tinker with it as if it was an (Excel) spreadsheet.

    if all your users need to to view the data then consider generating a report and then sending it to them as a snapshot or possibly a PDF. if they want to manipulate it then I'd suggest you send it to them as a spreadsheet, or you allow them to run the query in Excel to extract the data as required. it depend son your users, your confidence in them, your experience and the time available. over the years I've had great success in duming stuff into Excel, lettign users do whatever the heck they want in Excel but never ever updating the underlying data. I don't trust users of spreadsheets to respect the data so its a one way street.. they get what they need, they can tinker to their hearts content but they only update data through a db interface.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    39
    Thanks Healdem, I understand what you're saying and have no wish to let the end users of the data get their sticky fingers on the data in a way they can mess things up.
    I simply want them to end up with as user friendly a lump of data as possible achieved in as few steps as possible on my part.
    Bottom line seems to be that Access conditional formatting can't be exported with the object that is being exported?
    For my purposes I therefore need to export the query as a spreadsheet and format it then.
    Thanks
    Keith

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..or send them the report as an aAccess snapshot or PDF.

    if you want them to have a pretty layout in Excel then you may need to write the Excel spreadhseet yourself, theres some stuff in the code bank from Pootle Flump which could be of use to you if you go down that route
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2009
    Posts
    39
    Thanks Healdem, I'll search for Pootle Flump.
    The font colour issue may be 'pretty' to you and I but it does have a function for stressed managers with very limited IT skills in that it allows them to peruse a single list of all their employees and get an easy visual clue as to where people stand in relation to, in this instance, essential training refresher dates.
    I've learned never to underestimate the benefits of displaying things in bright primary colours, the higher up the ladder the manager is the more the colours seem to be appreciated

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or design a form which is searchable, so that they can sort their data in any order they wish, colours then become less important as the data is presented in the sequence of relevance. unless they are wedded to paper user driven froms may well be the way to go. and if you 'must' ise colour you can use colour by conditional formatting
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •