Calculating Time durations with Nul values

  • Thread starter Thread starter Eric Blitzer
  • Start date Start date
E

Eric Blitzer

I have a query that calculates hours worked based on the
Time In(ATimeIn, the Timeoutfor lunch(ATimeOut, the
TimeBack from lunch(BTimeIn), and the time they
leave(BTimeOut). the problem is when someone only works a
half day. How is the way to deal with null value in the
following calculation.

TotalHours:
(DateDiff("n",[ATimeIn],[ATimeOut])+DateDiff("n",[BTimeIn],[BTimeOut]))/60

Thanks

Eric
 
You either have to write some checks into the expression
that turns the nulls into 0, or do the calculation on
other variables:

ATimeIn-NoNull: iif(isnull([ATimeIn]),0,[ATimeIn])
 
Try NZ() function to translate null values into 0

(NZ(DateDiff("n",[ATimeIn],[ATimeOut]))+NZ(DateDiff("n",[BTimeIn],[BTimeOut]
)))/60

Tonín :-)


"Eric Blitzer" <[email protected]> escribió en el mensaje
I have a query that calculates hours worked based on the
Time In(ATimeIn, the Timeoutfor lunch(ATimeOut, the
TimeBack from lunch(BTimeIn), and the time they
leave(BTimeOut). the problem is when someone only works a
half day. How is the way to deal with null value in the
following calculation.

TotalHours:
(DateDiff("n",[ATimeIn],[ATimeOut])+DateDiff("n",[BTimeIn],[BTimeOut]))/60

Thanks

Eric
 
:-D

Just a little comment:

Nz() can be a great function :-) *unless* you are gonna query from
Excel a Nz()-based query made in Access. Excel will not recognize such a
Nz() function and will prompt an error similar to "Undefined Nz() function!
Go back to the IIf syntax!" :-D


Tonín
 
Back
Top