Prev: Windows 7 Live & Google Around The Globe
Next: Dynaset Automation Object
From: Gordon on 14 May 2010 07:52
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
From: Jerry Whittle on 14 May 2010 10:33
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" wrote:

> 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
> .
>
From: Gordon on 14 May 2010 14:20
On May 14, 3:33 pm, Jerry Whittle
<JerryWhit...(a)discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
> > .- Hide quoted text -
>
> - Show quoted text -

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

Gordon
 | 
Pages: 1
Prev: Windows 7 Live & Google Around The Globe
Next: Dynaset Automation Object