Apply conditional formatting to report field if contains current date

[gaker10]

I have a report which has a field where I can put any kind of text to suit deadlines. I'll put "DUE [DATE] [some other text]" if something needs to be done, and just "[DATE] [Completers Initials] [different sort of text]" when it is complete. The text components mentioned above can be any string for my notes.

I would like to add conditional formatting to this field as follows:

If the field contains "DUE [Today's Date]", highlight it red and bold.
If the field contains "DUE [Before Today's date]", highlight it pink.
If the field contains "DUE [Within 7 days of todays date]", highlight it yellow.


If the field contains "DUE [Beyond 7 days from todays date]", highlight it green.

I have this conditional formatting setup for a strictly date formatted field, but not a field that could have any string of text.
I'm really just having trouble getting the date part setup in the expression. Tried object indicator ("&"?) and got an error of some sort.

Thank you in advance.

[gaker10]

Also, the bracketed items above are not field names, just dates formatted as follows: mm/dd/yyyy

[ItsMe]

I cannot remember what Access 2010 conditional formatting wizard looks like. In 2013, it spells everything out and you have pulldowns for Not Equal To, Greater Than, etc.

All you really need to do is use the Date function. To get today's date, you would use Date(). Otherwise you should be able to do simple math
Date() -7
Date() +7

[gaker10]

ItsMe, I understand the math of using the date function, but not the syntax.

Screenshot of conditional formatting: Since this is an InStr() type deal, I have to use "Expression Is.." instead of "Field Value"...


This did not work, I get a "too many parenthesis" error.
But this looks like what I need. My syntax must be off.

[ItsMe]

OK, you have a syntax error. But that is not your biggest problem. Can you show some sample data from BOM Completed? Does this field ever have text other than DUE and a date?

I saw your explanation in post #1, but I am concerned how you typed the dates into the text field. Comparing a Date data type to a text field is not straight forward.

[gaker10]

Yes, it does.
It will have either of the following (including being blank):
"DUE [MM/DD/YYYY] [TEXT]"
"[MM/DD/YYYY] [TEXT]"

Examples:

12/7/2014 AAA
DUE 11/15/2014 TEMPLATE A ONLY
DUE 11/21/2014 +/- 1 DAY
DUE N/A
11/10/2014 AB TEMPLATE C ONLY, 11/15/2014 TEMPLATE D ONLY

That is correct, that is why I kept my rule as "DUE..." and not just if it equals a date. Perhaps I need to add a formatting function for the date part so that it reads that portion of the cell as a date. One thing is for sure, the text will always be "DUE [Date] blahblahblah" or just "[Date] blahblahblah", and my only concern are cells with "DUE" in them, so the ones with just "[Date]" are cells I don't need formatting for.

Does this clarify?

[gaker10]

Exception to post #6 is the "DUE N/A" example. Just skip that one for the conditional formatting rule.

[ItsMe]

You could do something like the following
InStr([BOM Completed], "Due " & Format(Date, "Short Date"))

Also, you should be able to add a second rule for the DUE N/A

[gaker10]

Tried that. Nothing happens. Also it keeps putting double quotes around "Date".

[ItsMe]

...Also it keeps putting double quotes around "Date".

Oh, hmmm. Maybe try it using parenthesis to evoke the function Date().

InStr([BOM Completed], "Due " & Format(Date(), "Short Date"))

[gaker10]

That did it

Final: InStr([BOM Completed],"DUE " & Format(Date(),"Short Date"))>0

Thank you!

[ItsMe]

You bet, just be aware that any variation in that field, like a missing space or a double space, will provide unexpected results. It is a very fragile rule.

Also, in case you have not figured out the adding days thing, something like this should od the trick ...
InStr([BOM Completed],"DUE " & Format(Date() + 7,"Short Date"))>0

And actually, I would guess the > operator would not be necessary to evaluate 0. I would expect it to automatically evaluate for False. But, I do not work with the expression builder often.