Results 1 to 9 of 9
  1. #1
    Harley Guy is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2010
    Posts
    57

    Conditional Formatting based on another field - Part II

    I thought I had this figured out but no...

    Despite the excellent help from SSANFU, I'm still not able to get this to work. While I would like to upload a sample database, I keep getting an error and it's not able to upload.

    I believe it was my fault for not being specifically clear...that I already had a database created and needed to add this option.

    What I would like to have happen is this. Each person trained is evaluated and given a level (1 - 4) and is conditionally formatted based on the level given. However, management has asked if we could show last date trained and have that field formatted conditonally as well. For instance, if the last date trained was 8 weeks ago, the date field would be red, 6 weeks ago yellow, 4 weeks ago blue and 2 weeks ago green.

    The reason I need the training levels to stay as they are is because our leads evaluate their workers as to what level they are at. Naturally, someone just learing the job would be a 1 (red) but his training record would be the current date (green). Conversely, an experienced employee could be a 4 (green) but the last time training was updated was over 8 weeks ago (red) so that would mean the employee needs have a refrehser on the task and the date would change to current (back to green).

    I am not sure if this is merely a conditinal formatting issue or if coding is required. I would also need to have the same information appear in any form or report that is run.



    I know I am asking the world on this but I am banging my head against the wall and am not getting any support from our IT department...this is too low on their priority list.

    Thanks in advance to anyone who can help!
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To format a different field you use Expression Is and an expression like:

    [Level]=1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Harley Guy is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2010
    Posts
    57
    It's not the training level that I am worried about.

    What I need to have change color is the date field. If training is 8 weeks old, date field needs to be red. Between 6-8 weeks, blue. 4-6 weeks, yellow and then anything less than 4 weeks, date field would be green.

    Is this possible?

    Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sure:

    [TrainingDate]<DateAdd("d",-56,Date())

    And apply them in the order you want them tested.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try

    Default: Red

    Condition 1: Green
    Field Value Is greater than or equal to DateAdd("ww",-4,Date())

    Condition 2: Blue
    Field Value Is greater than or equal to DateAdd("ww",-6,Date())

    Condition 3: Yellow
    Field Value Is greater than or equal to DateAdd("ww",-8,Date())
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll stay out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Didn't see your last post Paul, before adding mine. The choice of operator had me thrown for a bit. Conditions based on date calcs always twist my brain. Had to actually set up the conditionals and test. They seem to work. Although use of day unit might be better.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry I misunderstood.

    You probably have this under control, but here is the dB with the additional formatting.

  9. #9
    Harley Guy is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2010
    Posts
    57
    All right! Got to work and thanks everyone for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-15-2013, 07:49 AM
  2. Replies: 11
    Last Post: 12-21-2011, 09:48 AM
  3. Replies: 18
    Last Post: 09-30-2011, 06:41 AM
  4. Replies: 2
    Last Post: 09-08-2011, 01:10 PM
  5. Replies: 0
    Last Post: 03-14-2011, 08:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums