Conditions with a nested IIF

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
 
J

Jerry Whittle

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.
 
G

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.
--
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).
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?

Gordon
.- Hide quoted text -

- Show quoted text -

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

Gordon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top