Conditional Format Issue

[chrisline90]

Hi,

I have an Access DB that tracks expiry dates. Lots of expiry dates for several hundred employees. I produce reports for Managers so they can see when people are approaching specific expiry dates so they can take action and book courses etc.
I had a request from the managers to format the reports so that dates are highlighted in this way:

• Dates due to expire THIS month
• Dates due to expire NEXT month (+1)
• Dates due to expire the MONTH AFTER (+2)

I used the Month and Year Function to extract the Month and Year (in this example ATCLM and ATCLY) from the (ATCL) date, then in the report I set up conditional formatting to highlight using the following:

This works and gives me the formatting I need for:

• This Month
• Next Month (+1)
• The Month After (+2)
• Dates that have expired

...For the current YEAR...

The problem is, how do I get round the problem in November and December when the formatting rules for NEXT MONTH (+1) and THE MONTH AFTER (+2) need to account for the incremented YEAR for the +1 (in December) and +2 dates (in November and December).

I would be very grateful for any advice.

 

[arnelgp]

For next month, the Expression is:

Format([ATCLY],"0000") & Format([ATCLM],"00") = Format(DateAdd("m",1,Date()), "yyyymm")

For 2 months:

Format([ATCLY],"0000") & Format([ATCLM],"00") = Format(DateAdd("m",2,Date()), "yyyymm")

 

[cheekybuddha]

Just in case your ATCLM field is an actual number rather than a two digit string, you may have to adjust Arnel's suggestion to:

For next month, the Expression is:

[ATCLY] & Format([ATCLM], "00") = Format(DateAdd("m",1,Date()), "yyyymm")

For 2 months:

[ATCLY] & Format([ATCLM], "00") = Format(DateAdd("m",2,Date()), "yyyymm")

 

[cheekybuddha]

Or you could just use the ACTLExpiry field directly and remove the need for separate Year and Month fields:

For Due to expire this month:

Format([ACTLExpiry], "yyyymm") = Format(Date(), "yyyymm")

For next month, the Expression is:

Format([ACTLExpiry], "yyyymm") = Format(DateAdd("m",1,Date()), "yyyymm")

For 2 months:

Format([ACTLExpiry], "yyyymm") = Format(DateAdd("m",2,Date()), "yyyymm")

 

[CJ_London]

Or just use the dateadd function

Expdate<dateadd(“m”,1,date())

 

[chrisline90]

Thank You all so much for your prompt and excellent advice. I will implement immediately .

 

[DHookom]

I would create a function that accepts the ATCL date field and returns either number of months or number of days like 0, 30, 60, 90, This solution creates one place to modify the calculation WHEN the managers change their minds. Save the new function in a module named modBusinessCalcs