Results 1 to 7 of 7
  1. #1
    oldager is offline Novice
    Windows 10 Access 2003
    Join Date
    Aug 2020
    Location
    Ireland
    Posts
    7

    Conditional formatting DateAdd

    On my form I have 2 controls:
    Date (which is a date some time in the past say 20/1/2020 - English date format) and


    Available (which is also a date close to today's date say 06/08/2020)

    I am trying to conditionally format the [Date] control to be orange with any date in 2019 up to about 6 months before the date in the control [Available], that is about 06/02/2020.

    I have thus far got: If Value is between InStr([Date],"2019") and DateAdd("q",-2, [Available])

    The result is not giving the orange colour assigned but is reverting to "The format to be used if no conditions are met".

    So, may I ask why is my condition not being met. Where have I gone wrong in my "if Value is ..."

    Any help greatly appreciated (from a 74 year old ager).

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    InStr() is a string function, not a date function. Maybe try:

    between #1/1/2019# and DateAdd("q",-2, [Available])
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, "Date" is a not a good choice for a field name, as it can conflict with the Date() function.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    oldager is offline Novice
    Windows 10 Access 2003
    Join Date
    Aug 2020
    Location
    Ireland
    Posts
    7
    Still not working correctly. I changed "Date" to [DaftEntered].
    I have changed all my dates to omit any preceeding 0s eg. 08/08/2020 to 8/8/2020 which has made a difference to more recent 2020 dates in DaftEntered so they work correctly.

    Thus I now have:
    between #1/1/2019# and DateAdd("q",-2, [Available]) - That is 6 months before date Available Which is about 8/8/2020

    2019 Dates that do not change to orange but should: 29/9/2019 - 20/6/2019 - 29/10/2019 - 11/10/2019
    Some 2019 dates working correctly (changing to orange): - 23/5/2019 -06/9/2019 - 26/6/2019
    Some 2020 dates working correctly (not changing to orange: 22/7/2020 - 2/2/2020 - 10/6/2020
    Some 2020 dates that should change to orange but do not: 20/1/2020

    All the above dates were copied and pasted so there should be no mistakes. There seems to be no consistancy in which dates work correctly and those which do not!!

    Any further help much appreciated and many thanks for the above help.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    When you say you omitted the leading zeros it makes it sound like the data type of the field is text, not date/time. Is that the case? If so you'll be getting an alphabetic comparison instead of a date comparison. Can you attach the db here if that's not it?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    oldager is offline Novice
    Windows 10 Access 2003
    Join Date
    Aug 2020
    Location
    Ireland
    Posts
    7
    So, I changed both controls to date/time and now everything is working perfectly.

    Very many thanks for all your help and the speedy replies.

    Kind regards, oldager.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 11-12-2015, 10:46 AM
  2. Need Help With Conditional Formatting
    By Ursa2312 in forum Access
    Replies: 3
    Last Post: 07-28-2015, 11:18 AM
  3. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  4. Conditional Formatting
    By Paul H in forum Reports
    Replies: 3
    Last Post: 11-07-2011, 11:59 AM
  5. Conditional Formatting
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-30-2010, 09:31 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