Conditional Formatting on Dates

[jpicard]

I am trying to apply conditional formatting to a field on a report that is a date from the underlying table (field name [maturity date3]). I want to highlight the field green if [maturity date3] is within 30 days from a specified date, red if it is within 60 days and yellow if it is within 90 days. (i.e., I want to tell the computer to compare the date in [maturity date3] to "10/1/2011" and highlight the field green if [maturity date3] is between 10/1/11-10/31/11, red if [maturity date3] is between 11/1/11-11/30/11, and yellow if [maturity date3] is between 12/1/11-12/31/11.



How do I enter this information in the Rule window for Conditional Formatting? I have tried various expressions with no result and I am really getting frustrated. Further complicating this may be the fact that [maturity date3] may be left blank as well.

I'd appreciate any help that can be offered!

[June7]

That is simply:
Field Value is between #10/1/2011# and #10/31/2011#
and same for the other date ranges. But this is hard coding a date range. You would have to modify the conditions each month.

Maybe what you really want to know is the status of MaturityDate in relation to the current date? Is it past due, due in 30 days, 60 days, 90 days? That would be:
Field Value Is less than Date()
Field Value Is between Date() and Date() + 30
Field Value Is between Date() and Date() + 60
Field Value Is between Date() and Date() + 90

[Toyman]

I am trying to apply conditional formatting to a field on a report that is a date from the underlying table (field name [maturity date3]). I want to highlight the field green if [maturity date3] is within 30 days from a specified date, red if it is within 60 days and yellow if it is within 90 days. (i.e., I want to tell the computer to compare the date in [maturity date3] to "10/1/2011" and highlight the field green if [maturity date3] is between 10/1/11-10/31/11, red if [maturity date3] is between 11/1/11-11/30/11, and yellow if [maturity date3] is between 12/1/11-12/31/11.

How do I enter this information in the Rule window for Conditional Formatting? I have tried various expressions with no result and I am really getting frustrated. Further complicating this may be the fact that [maturity date3] may be left blank as well.

I'd appreciate any help that can be offered!

You can also use this:

DateDiff("d",[maturity date3],Date())<30

[jpicard]

I tried Field Value is Between #10/1/2011# and #10/31/2011#, but it didn't work.

I then tried the other suggestions, but nothing seems to be working. What am I doing wrong?

[June7]

Don't know, works for me. Do you want to provide the project for analysis? Copy, remove confidential data, run Compact & Repair, zip if still large, attach to post. Attachment manager is below the Advanced post editor.

[jpicard]

OK, I found my problem...apparently, the shape fill was formatted to transparent instead of automatic, which was over-riding the conditional formatting application of colors!

Now, another question - how do I tell it to use a specific date instead of just today's date?

[June7]

By hard code as demonstrated or reference to another field in the RecordSource. What determines the date to be used as criteria?

[jpicard]

If I run this report on 11/20/11, I want it to use 30, 60 & 90 days from 11/1/2011

[June7]

Okay.
Field Value Is Between CDate(Month(Date()) & "/1/" & Year(Date())) and CDate(Month(Date()) & "/1/" & Year(Date()))+30

Or
Field Value is between DateSerial(Year(Date()),Month(Date()),1) and DateSerial(Year(Date()),Month(Date()),1)+30

[jpicard]

That is extremely helpful. If I could ask for one last variation - how do I tell it a specific date to use? I imagine it is some variation on a parameter query, but I can't figure out how to set it up.

[June7]

The conditional rule would have to refer to field in the RecordSource that would provide the criteria value. Again, what criteria would determine the date value? Perhaps a value input on a form or by parameter input prompt in query could become the criteria value. Construct a field in query with expression. The same value will be available to every record in the query.

DateCriteria: [Forms]![formname]!controlname
or
DateCriteria: [enter date]

Now refer to that constructed field in place of the Date() reference

I don't use parameter input prompts because can't control validation. I would use a form.

[jpicard]

Thank you so much for all your help (and patience) today June7! You have been a huge help to me.

[June7]

Just finished testing. Had better luck with the Expression Is option.

[maturity date3] Between DateSerial(Year([DateCriteria]),Month([DateCriteria]),1) And DateSerial(Year([DateCriteria]),Month([DateCriteria]),1)+30