Results 1 to 6 of 6
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Conditional Formatting On Sub Form


    I am attempting to use Conditional Formatting on a Sub-Form that I have with a concatenation of two fields - month & year and hardcoding 1 as the day so it is like this
    Code:
    Date([Month] & "/01/" & [Year]) As InvoiceDate

    Now on my form I want to use an expression to
    Turn Red if the InvoiceDate is >= 60 days of the 1st of the current month
    Turn Yellow if the InvoiceDate is between 30 days and 60 days of the 1st of the current month


    I Have tried
    Expression: [InvoiceDate] < Date()-60 .... for Red
    Expression: [InvoiceDate] Between Date()-30 And Date()-60 ....for Yellow

    BBBUUUUTTTT this takes into account the current date and not the 1st of the month, so my coloring is skewed. I tried to use DateSerial() in my Expression but then NOTHING is colored, almost like the Conditional Formatting does not recognize it. I'm at a loss at this point!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Date() function cannot be used as shown in first expression. All Date() does is return current date - the function does not have arguments.

    Is this your thread https://access-programmers.co.uk/for...d.php?t=305866
    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.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Hi - thank you for the response.

    No that thread is not me. I did see it when I did an internet search and tried the suggestions but was unsuccessful in my attempts.

    If Date() is not the proper function to use to achieve my desired easily, how should I do this?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The thread shows solved so why does it not work for you?

    Did you try this, does apply color for me:

    InvoiceDate < DateSerial(Year(Date()), Month(Date()), 1) - 60

    InvoiceDate BETWEEN DateSerial(Year(Date()), Month(Date()), 1) - 30 AND DateSerial(Year(Date()), Month(Date()), 1) - 60
    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.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    FYI Month and Year are also functions and should not be used as field names - rename as invMonth, invYear or similar

    try

    Turn Red if the InvoiceDate is >= 60 days of the 1st of the current month - field value is....greater than.....date()-day(date())+60
    Turn Yellow if the InvoiceDate is between 30 days and 60 days of the 1st of the current month -field value is....greater than.....date()-day(date())+30

    providing you do the red as the first condition, it will already take out the >60, so you have basically the same formula for the second condition

    Note that your formula is looking at invoice dates in the future - is that what you mean? Or do you want to know which ones are overdue? If so the formula would be

    Turn Red if the InvoiceDate is <= 60 days of the 1st of the current month - field value is....less than.....date()-day(date())-60
    Turn Yellow if the InvoiceDate is between 30 days and 60 days of the 1st of the current month -field value is....less than.....date()-day(date())-30

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Quote Originally Posted by Ajax View Post
    FYI Month and Year are also functions and should not be used as field names - rename as invMonth, invYear or similar

    try

    Turn Red if the InvoiceDate is >= 60 days of the 1st of the current month - field value is....greater than.....date()-day(date())+60
    Turn Yellow if the InvoiceDate is between 30 days and 60 days of the 1st of the current month -field value is....greater than.....date()-day(date())+30

    providing you do the red as the first condition, it will already take out the >60, so you have basically the same formula for the second condition

    Note that your formula is looking at invoice dates in the future - is that what you mean? Or do you want to know which ones are overdue? If so the formula would be

    Turn Red if the InvoiceDate is <= 60 days of the 1st of the current month - field value is....less than.....date()-day(date())-60
    Turn Yellow if the InvoiceDate is between 30 days and 60 days of the 1st of the current month -field value is....less than.....date()-day(date())-30
    This worked perfectly, thank you kindly

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

Similar Threads

  1. Conditional Formatting on a form
    By AndyC121 in forum Forms
    Replies: 1
    Last Post: 03-23-2017, 09:13 AM
  2. Conditional Formatting in a Access Form
    By adillpickle in forum Forms
    Replies: 3
    Last Post: 01-26-2016, 10:55 AM
  3. Replies: 6
    Last Post: 05-26-2013, 09:10 PM
  4. Replies: 7
    Last Post: 10-01-2012, 07:59 AM
  5. Conditional formatting on form
    By ngruson in forum Forms
    Replies: 11
    Last Post: 09-17-2010, 12:15 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