TimeValue and #Error in null fields

  • Thread starter Thread starter shelter
  • Start date Start date
S

shelter

I have two time fields ([DispatchedTime] and [ArrivedTime]), where some
of the data contains both the date and time (ex: 10/15/2006 11:37:12
PM), some is just the time, and some is null (ugh). I need the data as
time only, in order to calculate the difference between the two time
fields. I used TimeValue to convert the data to time only, but it is
generating a #Error in the fields that are null. I tried wrapping the
argument in Nz, but am still getting the #Error. Here is what the
argument looks like in the query:
DispTime: TimeValue(nz([dbo_tbldispatchdetail.DispatchedTime],""))

I also tried
DispTime:
TimeValue(nz([dbo_tbldispatchdetail.DispatchedTime],"00:00:00")), but
it shows up in the query results as 12:00:00 AM.

I appreciate any ideas or solutions you have.
 
Thank you for your quick responses.
00:00:00 is 12:00:00 AM.

Dates are stored as floating point decimal where the integer part represents
the date and the decimal part represents the time. Since a day starts at
12:00:00 AM, you will always get 12:00:00 AM if you look at the TimeValue of
a date data type that has no time.

--
Dave Hargis, Microsoft Access MVP


[email protected] said:
I have two time fields ([DispatchedTime] and [ArrivedTime]), where some
of the data contains both the date and time (ex: 10/15/2006 11:37:12
PM), some is just the time, and some is null (ugh). I need the data as
time only, in order to calculate the difference between the two time
fields. I used TimeValue to convert the data to time only, but it is
generating a #Error in the fields that are null. I tried wrapping the
argument in Nz, but am still getting the #Error. Here is what the
argument looks like in the query:
DispTime: TimeValue(nz([dbo_tbldispatchdetail.DispatchedTime],""))

I also tried
DispTime:
TimeValue(nz([dbo_tbldispatchdetail.DispatchedTime],"00:00:00")), but
it shows up in the query results as 12:00:00 AM.

I appreciate any ideas or solutions you have.
Click to expand...
Click to expand...
 
Back
Top