Condition format a date field (1 Viewer)

[virencm]

Hello,

I am after some help with condition formatting a date field in access.
The query used for this field produces a date or "NA" based on the formula below. so when the date shows up as "NA" then i have set the condition formatting of the cell to grey and this works works well. but when it shows up with a date it doesnt format to a grey .

the formatting pane has these two expressions

1. Q_Induction_Date = "NA" then grey the cell

2. Q_Induction_Date < DATE() then grey the cell and this DOESNT work. i have also tried the function Now() and that doesnt work either.

Formula i use in the query is

Q_Induction_Date: IIf([y].[INDUCTION_NA]=-1,"NA",IIf(IsNull([induction_date]),"NA",Format([induction_date],"dd/mm/yyyy")))

Appreciate the time and help.

Cheers
V

 

[CJ_London]

can you clarify what you mean by 'set to grey' - do you mean you are disabling the control?

Also, your format in your query is converting a date to a text value so 'Q_Induction_Date < DATE()' is comparing a text value to a date value.

you could try

CDate(Q_Induction_Date) < DATE()

 

[arnelgp]

as said the data type now of Q_Induction_Date is of string.
then your second formatting condition would be:

Format(Q_Induction_Date, "yyyymmdd") < Format(Date, "yyyymmdd")

 

[Galaxiom]

Would not INDUCTION_NA = True mean InductionDate would consequently be Null?

You can display "NA" for Nulls by simply using the Format property of the control or field.

dd/mm/yyyy;;;"NA"

The Conditional Format would also be simple.
Enable on the condition:

Nz(datefield,0)>Date()

(This would work provided your Induction dates were not prior to the 20th century.)

Moreover this scheme allows you to change the future induction dates through a bound control where your original concept uses a calculated field and hence would not be updateable, making the Enabled/Disable facility somewhat pointless.

 

[virencm]

CDate(Q_Induction_Date) < DATE() works.. excellent. thanks CJ _london..


Thanks for your reply Arnelgp & Galaxiom.

regards
V