Results 1 to 15 of 15
  1. #1
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476

    Using Conditional Formatting & Dates

    Using Conditional Formatting in either a Form &/or in a Report, how can I color code/highlight/shade certain “DATES” which are X-amount of days old/over?

    I.E.

    Dates that are < (less&shy than 15-days – highlight/shade in light blue

    Dates that are < (less&shy than 30-days – highlight/shade in light yellow

    Thanks

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    does the conditional formatting wizard accept functions??

    if it does, you can use DATEDIFF() or DATEADD() to figure the differences.

  3. #3
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    I can't figure it out & get it to work for me

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    and you have tried what exactly? see here: https://www.accessforums.net/tutoria...l#itdoesntwork

  5. #5
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476

    Lost

    Adam,

    I tried using the DATEDIFF() or DATEADD() with Conditional Formatting, but I can't figure the differences. With the DATEADD(), I don't know what to put for "interval"? You know, I thought that I had accomplished this task in another database, which I no longer obtain, but I din't think that it was very involved.

    BTW, in this original thread I felt that I was very precise in what I was asking.

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    try something like this in the conditional formatting wizard:

    if VALUE is LESS THAN

    Code:
    dateadd("d", -15, date())
    SPECIFY CONDITION HERE

    does that make sense?? in the condition text box, that's where you use the date function as I have shown.

  7. #7
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    I Tried-
    [MaxOfDW] «Expr» DateAdd("d", -15,date())

    I'll get back to this tomorrow.

    Thanks

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    no dude,

    the CODE block of my post is the only stuff you need to enter in the blank text box. The rest of it is provided to you in the form of dropdown choices, right?

    enter this in the box:

    Code:
    DateAdd("d", -15,date())

  9. #9
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    I thank you for your patience with me & please forgive my ignorance, but I’m not quite following you. First off, I’m using Access 2010 & I’m using the “Conditional Formatting New Rules” dialog box. You mentioned something about “Conditional Format Wizard;” are we talking about the same thing. I don’t believe to have this wizard. ’m sorry, I don’t know where I’m supposed to put this code - dateadd("d", -15, date())

    Dave...

  10. #10
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    sorry Dave, I only had 2010 for a short while.

    I think the best thing you can do is post a screenshot of the conditional formatting dialog screen that 2010 gives you. If I can see all of those boxes, I can tell you where that expression goes. I think...

    you're right...the interface of that thing is night and day between 07 and 10. it completely changed

  11. #11
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    If you will, please direct me in how I "post a screenshot of the conditional formatting dialog screen " for you/to send you.

  12. #12
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by djclntn View Post
    If you will, please direct me in how I "post a screenshot of the conditional formatting dialog screen " for you/to send you.
    press the PRINT SCREEN button when you have it open. then paste it into the PAINT program, or other image-friendly app.

    then host it on photobucket or upload it here to thread.

  13. #13
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    I belive to have sent you the attachment in Paint

  14. #14
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by djclntn View Post
    I belive to have sent you the attachment in Paint
    push the NEW RULE button sir, and then take the pic. that screen doesn't let me see much.

    by the way, are you trying to add conditional formatting rules to a DATASHEET form?? I don't believe it works in that view.

  15. #15
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Adam,

    I got it!

    In the Conditional Format Rules Manager I put:

    Less than - Now()-15 & highlighted in in light red (highlights in light red the dates 15-days past as of today/the current date)

    Less than - Now()-30 & highlighted in in light green. (highlights in light green the dates 30-days past as of today/the curret date).

    Thank you so much in your patience & perseverance with me!

    Dave...

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

Similar Threads

  1. Conditional Formatting
    By Desstro in forum Programming
    Replies: 3
    Last Post: 12-01-2010, 09:52 PM
  2. Conditional Formatting
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-30-2010, 09:31 PM
  3. Conditional Formatting
    By DanOzDirect in forum Reports
    Replies: 3
    Last Post: 07-21-2010, 08:49 PM
  4. Conditional Formatting
    By cevatyildiz in forum Forms
    Replies: 8
    Last Post: 04-30-2010, 12:01 PM
  5. Conditional formatting
    By ylivne in forum Reports
    Replies: 1
    Last Post: 07-12-2009, 06:18 PM

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