how would I write this criteria for a query?

[Guest]

I have three fields that I want to base a criteria on:

Received Date/Time, Date Approved and Date Rejected

The way I wrote the criteria is as follow:
LagTime: [Received Date/Time] - [Date Approve]

This works if there is a date in the "Date Approve" field but if there isn't
a date in the field nothing comes back. What I would like for it to do is
pull the date from the "Date Rejected" field and calculate the lagtime from
there.

What do I need to add to my expression to do this?

 

[Guest]

LagTime: IIf(IsNull([Date Approve]) = Yes, [Received Date/Time] - [Date
Rejected], [Received Date/Time] - [Date Approve])

 

[Jamie Collins]

LagTime: IIf(IsNull([Date Approve]) = Yes, [Received Date/Time] - [Date
Rejected], [Received Date/Time] - [Date Approve])

I'm thinking that

IsNull([Date Approve]) = Yes

is a typo ;-)

Either use

IsNull([Date Approve])

or more portable (and IMO more readable)

[Date Approve] IS NULL

Jamie.

--

 

[Guest]

Doh! I meant "True" and not "Yes". Actually -1 would be even better.

While plain old IsNull([Date Approve]) will work, somewhere along the line I
got burnt by something like it. Now I always put in the True or False part.

Thanks for the correction.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

LagTime: IIf(IsNull([Date Approve]) = Yes, [Received Date/Time] - [Date
Rejected], [Received Date/Time] - [Date Approve])

I'm thinking that

IsNull([Date Approve]) = Yes

is a typo ;-)

Either use

IsNull([Date Approve])

or more portable (and IMO more readable)

[Date Approve] IS NULL

Jamie.

--