Conditional Formatting on Date

[scouser]

Hi. I am attempting to format a date field on a report to display font in 'Red' background 'Grey' if the field value is less than Date() (i.e. today's date)

The underlying query lists computer details with a field for WarrantyExpiry. The report data displayed is dependent on date range entered via a form.

Conditional Formatting on field WarrantyExpiry:

Field Value Is less than Date()

Between [Forms]![REVIEW_WARRANTY_EXPIRY]![txtClick1] And [Forms]![REVIEW_WARRANTY_EXPIRY]![txtClick2]

The output is not as expected. If the user enters Start Date 14/08/2008 End Date 31/12/2008 the Warranty Expiry field lists:

15/12/2008 (Normal Font)
15/12/2008 (Normal Font)
08/11/2008 (Red Font / Grey Background)
21/11/2008(Normal Font)
06/10/2008 (Red Font / Grey Background)
08/11/2008 (Red Font / Grey Background)
24/10/2008 (Normal Font)
15/12/2008 (Normal Font)
06/10/2008 (Red Font / Grey Background)

What am I missing?
Many Thanks,
Phil.

 

[namliam]

Your problem is a common one... a formatting issue.

15/12/2008 is date: 15 dec 2008
but
08/11/2008 is date: 11 aug 2008 NOT 08 nov 2008

This is happening because you are allowing Access to implicitly convert your text to a date, instead of forcing access to do it the right way.

The best way to solve this would be to change your text fields on your form to date fields. No more conversion would be needed and you would get the expected results

 

[scouser]

Data Type

Hi. The field in question does not have a Data Type as it is a calulated field in a query based on 2 other values:

WarrantyExpiry: IIf(IsNull([WarrantyPeriod]) Or IsNull([DatePurchased]),"",DateAdd("yyyy",[WarrantyPeriod],[DatePurchased]))

Can I define a Data Type?

Many Thanks,
Phil.

 

[namliam]

Well BOTH fieds are text fields currently... probably...
Both the 2 fields on the form and the one field in the query/report.

Why do this at all??
WarrantyExpiry: IIf(IsNull([WarrantyPeriod]) Or IsNull([DatePurchased]),"",DateAdd("yyyy",[WarrantyPeriod],[DatePurchased]))

This would have the same result, but much simpler:
WarrantyExpiry: DateAdd("yyyy",nz([WarrantyPeriod],0),[DatePurchased])
Plus it will put WarrentyExpiry as a date field instead of a text field in your other Iif solution.

Also check your fields on your form and make sure they indeed are date too.

 

[scouser]

Date Field

Thanks for the code update I will give that a try.

The Data Types for the underlying tables:

WarrantyPeriod (Number)
DatePurchased (Date)

Thanks,
Phil.

 

[scouser]

Date Field

The code works great and the formatting is now as expected.
Many Thanks,
Phil.

 

[namliam]

Great another problem resolved

Implicit conversions can be a headache... be mindfull of them