DateDiff Conditional Formating

  • Thread starter Thread starter g
  • Start date Start date
G

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
 
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.)
 
Thank You Ken. That's the key changing the format to number

Ken Snell (MVP) said:
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/




g said:
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
Click to expand...
Click to expand...
 
Back
Top