Conditional Format for Between Dates AND Another Criteria on a report

Hello!

My query:

EventDate
EventPeriodicity (in days)
DateDueateAdd("d",[EventPeriodicity],[EventDate])

So, for something that happens every year, it would look like 1/1/09,
365 (days), and DateDue 1/1/10.

On the report, I conditional format the DateDue field so that it is
shaded red if the item has exceeded its DateDue (Field Value is Less
than Date()), and shaded yellow if it is within 2 weeks of the DateDue
(Field Value is Between Date() and DateAdd("d",14,Date())).

The problem is that if EventPeriodicity = "0" (item does not repeat),
then DateDue is N/A. (The item only has to be completed once, so there
is no due date for the next time it should happen.)

How do i adjust the yellow for both conditions: a) Within 2 weeks of
DateDue AND b) EventPeriodicity"0" ??


Thanks for the help!

VR/Lost



I used Field Value Is between Date() and DateAdd("d",14,Date())

You will have to use the Expression Is option instead of the Field value is
option in the conditional formatting.

Expression is
[EventPeriodicity]"0" AND [DateDue] Between Date() and DateAdd("d",14,Date())

If EventPeriodicity is a number field instead of a text field then you will
need to remove the quote marks around the "0".

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Lostguy wrote:
Hello!

My query:

EventDate
EventPeriodicity (in days)
DateDueateAdd("d",[EventPeriodicity],[EventDate])

So, for something that happens every year, it would look like 1/1/09,
365 (days), and DateDue 1/1/10.

On the report, I conditional format the DateDue field so that it is
shaded red if the item has exceeded its DateDue (Field Value is Less
than Date()), and shaded yellow if it is within 2 weeks of the DateDue
(Field Value is Between Date() and DateAdd("d",14,Date())).

The problem is that if EventPeriodicity = "0" (item does not repeat),
then DateDue is N/A. (The item only has to be completed once, so there
is no due date for the next time it should happen.)

How do i adjust the yellow for both conditions: a) Within 2 weeks of
DateDue AND b) EventPeriodicity"0" ??


Thanks for the help!

VR/Lost



I used Field Value Is between Date() and DateAdd("d",14,Date())