Solved Conditional formatting not working with Calculated Date Field (1 Viewer)

[Lochwood]

MY query creates a calculated field based on 2 dates.. it takes the day and month from 1 field and the year from another to produce a renewal date. This works perfectly but i am trying to add conditional formatting to this field and it aint working.

I am thinking because it is a calculated field and not an actual formatted date field, that may be the issue. the query and the form show as medium date.

Sure there is a logical solution to this.

it is pretty random but future dates tend to be in red currently with the odd green one appearing.

 

[theDBguy]

Hi. That depends. Can you show us your calculation?

 

[Lochwood]

Hi. That depends. Can you show us your calculation?

Due_Date: IIf(Not IsNull([maxof1st_date]),Format(Day([MaxOf1st_Date]) & "/" & Month([MaxOf1st_Date]) & "/" & Year([MaxOf2ndDate])+1,"Medium Date"))

 

[Gasman]

PMFJI,
Format returns a string?
I would use DateSerial() ?

 

[theDBguy]

Due_Date: IIf(Not IsNull([maxof1st_date]),Format(Day([MaxOf1st_Date]) & "/" & Month([MaxOf1st_Date]) & "/" & Year([MaxOf2ndDate])+1,"Medium Date"))

Ah, there you go. As @Gasman said, you were using the wrong function, and that's why the CF was not working.

You could either change the function in the query or change the expression in the CF.

 

[Lochwood]

PMFJI,
Format returns a string?
I would use DateSerial() ?

PMFJI,
Format returns a string?
I would use DateSerial() ?

where would i add this to the current string please?

 

[Gasman]

where would i add this to the current string please?

Look here to how it should be used.

https://www.techonthenet.com/access/functions/date/dateserial.php

 

[MajP]

Or due it the other direction
Expression:Cdate([yourfield]) > Date
...

 

[Lochwood]

thanks Gasman.. worked a treat