Conditions with a nested IIF

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

I am trying to analyse in a report performance based on two date
fields (fldTargetDate and fldActualDate).

The control source for the particular control on the report is:

=IIf(IsNull([fldActualDate]),"n/a",IIf(DateDiff("d",[fldTargetDate],
[fldActualDate])>7,DateDiff("d",[fldTargetDate],
[fldActualDate]),"OK"))

Since the fldActualDate may at times not yet be filled in, I need to
test for a null value. but the code produces an "OK" result for every
row (regardless of whether the target is met or not or whether there
fldActualDate is null or not).

Can anyone spot the error in my formula?

Thanks

Gordon
 
See what date diff that you have and what nulls:

TheDiff: DateDiff("d",[fldTargetDate], [fldActualDate])

Sort by this field.

Nulls in the fldActualDate should produce a null. Maybe the diffs are < 7.
 
See what date diff that you have and what nulls:

TheDiff: DateDiff("d",[fldTargetDate], [fldActualDate])

Sort by this field.

Nulls in the fldActualDate should produce a null.  Maybe the diffs are < 7.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



Gordon said:
I am trying to analyse in a report performance based on two date
fields (fldTargetDate and fldActualDate).
Click to expand...
The control source for the particular control on the report is:
=IIf(IsNull([fldActualDate]),"n/a",IIf(DateDiff("d",[fldTargetDate],
[fldActualDate])>7,DateDiff("d",[fldTargetDate],
[fldActualDate]),"OK"))
Click to expand...

Since the fldActualDate may at times not yet be filled in, I need to
test for a null value.  but the code produces an "OK" result for every
row (regardless of whether the target is met or not or whether there
fldActualDate is null or not).
Click to expand...
Can anyone spot the error in my formula?

Gordon
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks. I'll give that a try when I get back to the office after the
weekend.

Gordon
 
Back
Top