Complex date query

P

Paul

I'm running a query with dates and not getting the results I want. I'll try
to explain what I have and what I need it to do:

The query has some expressions which give the differece betten to dates as a
number. Its got 3 differefnt expressions that do this for different dates.
There is a 4th expression that is meant to add the results of the three other
ones to give us a total time taken. It does work in some cases, except where
in the other expressions it might not have any dates to find out the
difference so it gives a blank field in the query, but when it does that
expression 4 (which adds the totals) gives us a blank field too (IE it wont
add blank field as 0).

I hope I've explaned that well enough for somebody to help. Its giving me a
right headache! Even our so called system support cant work it out. Any help
would be great.
 
A

ACG

Try using the Nz function around the datedif function.
The Nz function works:- Nz(a-b,0) where a and b are your
current statements and the ,0 is the value to return if
the answere is null (or blank).
 
J

John Vinson

It does work in some cases, except where
in the other expressions it might not have any dates to find out the
difference so it gives a blank field in the query, but when it does that
expression 4 (which adds the totals) gives us a blank field too (IE it wont
add blank field as 0).
Click to expand...

Wrap each of your DateDiff (or other) expressions which might return a
NULL in the NZ() function. Anything plus NULL is NULL.

Your final grand total field might be

TotalTime: NZ(DateDiff("h", [this], [that])) + NZ(DateDiff("h", [who],
[what])) + ...

John W. Vinson[MVP]
 
P

Paul

Would this work

NZ([date recieved]-[Date sent], 0)

Or should I be using the datediff anyway?

John Vinson said:
It does work in some cases, except where
in the other expressions it might not have any dates to find out the
difference so it gives a blank field in the query, but when it does that
expression 4 (which adds the totals) gives us a blank field too (IE it wont
add blank field as 0).
Click to expand...

Wrap each of your DateDiff (or other) expressions which might return a
NULL in the NZ() function. Anything plus NULL is NULL.

Your final grand total field might be

TotalTime: NZ(DateDiff("h", [this], [that])) + NZ(DateDiff("h", [who],
[what])) + ...

John W. Vinson[MVP]
Click to expand...
 
J

John Spencer (MVP)

Hard to say for sure, but the NZ function may help you to solve your problem.
 
J

John Vinson

Would this work

NZ([date recieved]-[Date sent], 0)

Or should I be using the datediff anyway?
Click to expand...

DateDiff is safer. A date/time field is stored as a Double Float count
of days and fractions of a day, so if either of the fields contains a
time portion you may not get the results you expect.

John W. Vinson[MVP]
 
Top