Conditional formating on calculated fields

[JaneP]

I've created a refresher date for a training course which is calculated by

RefresherDate: IIf([Training Record]!TRRefresherPeriod=0,"N/A",([Training
Record]!TRRefresherPeriod*365)+[Training Record]!CourseEndDate)

I now want to use conditional formating in a form to highlight when that
Refresher date is overdue ie in the past but I can't seem to get it to work
as it highlights when the date is in the past & in the future. Thinking it I
need to make sure the calculation is recognised as a date although it shows
in the date format. I've even added additional fields to the query to
calculate if Due or not & it did the same, showed Due where there was a date
given & blank when N/A.

Help please as I'm going mental!!!!

 

[Klatuu]

If your text box would evaluate to a date, conditional formatting will work;
however, it appears this is a calculated value that will not always return a
date value.
Your formula is inaccurate. There is a 75% chance the date range will
include a leap year which will throw the date off by a day. If you use this
formula, it will not:

RefresherDate: IIf([Training Record]!TRRefresherPeriod=0,"N/A",
DateAdd("yyyy", [Training Record]!TRRefresherPeriod, [Training
Record]!CourseEndDate))

It is always best to use Access date functions. They handle leap years and
the numbers of days in months automatically.

 

[JaneP]

I appreciated about the leap year issue but I've updated the query with the
query you gave but I'm still getting the problem with the conditional
formatting.

I've set the formatting as;

Field value is equal to "N/A" then fill white
Field value is greater than or equal to Date() then fill yellow

But where there is a date it fills yellow whether the date is in the past or
the future!

If your text box would evaluate to a date, conditional formatting will work;
however, it appears this is a calculated value that will not always return a
date value.
Your formula is inaccurate. There is a 75% chance the date range will
include a leap year which will throw the date off by a day. If you use this
formula, it will not:

RefresherDate: IIf([Training Record]!TRRefresherPeriod=0,"N/A",
DateAdd("yyyy", [Training Record]!TRRefresherPeriod, [Training
Record]!CourseEndDate))

It is always best to use Access date functions. They handle leap years and
the numbers of days in months automatically.
--
Dave Hargis, Microsoft Access MVP

I've created a refresher date for a training course which is calculated by

RefresherDate: IIf([Training Record]!TRRefresherPeriod=0,"N/A",([Training
Record]!TRRefresherPeriod*365)+[Training Record]!CourseEndDate)

I now want to use conditional formating in a form to highlight when that
Refresher date is overdue ie in the past but I can't seem to get it to work
as it highlights when the date is in the past & in the future. Thinking it I
need to make sure the calculation is recognised as a date although it shows
in the date format. I've even added additional fields to the query to
calculate if Due or not & it did the same, showed Due where there was a date
given & blank when N/A.

Help please as I'm going mental!!!!

 

[Klatuu]

Change it to:
Exppression Is DateValue(Forms!FormName!TextBoxName)>=Date()

As I stated previously, this is not a date type field, so it will not
evaluate to a date in the format you enter it.
--
Dave Hargis, Microsoft Access MVP

I appreciated about the leap year issue but I've updated the query with the
query you gave but I'm still getting the problem with the conditional
formatting.

I've set the formatting as;

Field value is equal to "N/A" then fill white
Field value is greater than or equal to Date() then fill yellow

But where there is a date it fills yellow whether the date is in the past or
the future!

If your text box would evaluate to a date, conditional formatting will work;
however, it appears this is a calculated value that will not always return a
date value.
Your formula is inaccurate. There is a 75% chance the date range will
include a leap year which will throw the date off by a day. If you use this
formula, it will not:

RefresherDate: IIf([Training Record]!TRRefresherPeriod=0,"N/A",
DateAdd("yyyy", [Training Record]!TRRefresherPeriod, [Training
Record]!CourseEndDate))

It is always best to use Access date functions. They handle leap years and
the numbers of days in months automatically.
--
Dave Hargis, Microsoft Access MVP

I've created a refresher date for a training course which is calculated by

RefresherDate: IIf([Training Record]!TRRefresherPeriod=0,"N/A",([Training
Record]!TRRefresherPeriod*365)+[Training Record]!CourseEndDate)

I now want to use conditional formating in a form to highlight when that
Refresher date is overdue ie in the past but I can't seem to get it to work
as it highlights when the date is in the past & in the future. Thinking it I
need to make sure the calculation is recognised as a date although it shows
in the date format. I've even added additional fields to the query to
calculate if Due or not & it did the same, showed Due where there was a date
given & blank when N/A.

Help please as I'm going mental!!!!