DateDiff Conditional Formating

[g]

Hi Experts,

Please help me out in my problem. I have a text box with a control source
that getting the datediff from two dates and change the background base from
it.

Control Source: IIf([Date1Name]Is Null,"Null",IIf([Date2Name]Is
Null,"Null",DateDiff("n",[Date1Name],[Date2Name])))

In my Conditional Formating:
Condition1| equal to | "Null"
textbackground color Gray

Condition2| Greater than or equal |0
textbackground color Green

If no condition met
textbackground color Red

How come my text back only turn red if it is 0. The "n" could be positive or
negative but I wanted that if it is negative it will turn Red. I'm using
a2000.

Thank you very much

 

[Ken Snell \(MVP\)]

Most likely because the textbox is seen as text data by ACCESS, not as a
numeric value.

Change your ControlSource to this:

Control Source: IIf([Date1Name]Is Null,Null,IIf([Date2Name]Is
Null,Null,DateDiff("n",[Date1Name],[Date2Name])))

Set the Format of the text box to Number.

Change the Condition 1 in the conditional formatting to this:
Condition1| Expression Is | Nz([NameOfYourTextbox], 99999999) = 99999999

(I'm assuming that you'll never have a value as large as 99999999 as a
result.)

 

[g]

Thank You Ken. That's the key changing the format to number

Most likely because the textbox is seen as text data by ACCESS, not as a
numeric value.

Change your ControlSource to this:

Control Source: IIf([Date1Name]Is Null,Null,IIf([Date2Name]Is
Null,Null,DateDiff("n",[Date1Name],[Date2Name])))

Set the Format of the text box to Number.

Change the Condition 1 in the conditional formatting to this:
Condition1| Expression Is | Nz([NameOfYourTextbox], 99999999) = 99999999

(I'm assuming that you'll never have a value as large as 99999999 as a
result.)

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Experts,

Please help me out in my problem. I have a text box with a control source
that getting the datediff from two dates and change the background base
from
it.

Control Source: IIf([Date1Name]Is Null,"Null",IIf([Date2Name]Is
Null,"Null",DateDiff("n",[Date1Name],[Date2Name])))

In my Conditional Formating:
Condition1| equal to | "Null"
textbackground color Gray

Condition2| Greater than or equal |0
textbackground color Green

If no condition met
textbackground color Red

How come my text back only turn red if it is 0. The "n" could be positive
or
negative but I wanted that if it is negative it will turn Red. I'm using
a2000.

Thank you very much