Converting a number into time

[jrayjr]

Hello,
I have a access query that reads in 2 time fields but its not in time format. Example: 900, 1300, 256, 2245, etc. What I am trying to do is driver comes in at 900(9:00AM) and leaves at 1300(1:00PM). Then have it give me the total time he was here, 4 hours

The build expression is as follows:

Total Time:
IIf([CARRIER_OFF_DOCK_TIME]>959,TimeSerial(Mid([CARRIER_OFF_DOCK_TIME],1,2),Mid([CARRIER_OFF_DOCK
_TIME],3,2),0),TimeSerial(Mid([CARRIER_OFF_DOCK_TIME],1,1),Mid([CARRIER_OFF_DOCK_TIME],2,2),0)) - IIf([CARRIER_ON_DOCK_TIME]>959,TimeSerial(Mid([CARRIER_ON_DOCK_TIME],1,2),Mid([CARRIER_ON_DOCK
_TIME],3,2),0),TimeSerial(Mid([CARRIER_ON_DOCK_TIME],1,1),Mid([CARRIER_ON_DOCK_TIME],2,2),0))

That should work but I get an error: "Data type mismatch in criteria expression."

The last thing I should mention is that I have no control over the time being read into the query. Its link to a table outside of Access.

Any insight would be great.
Thx,
John

[FishVal]

Hi, John.

Try the following to convert [CARRIER_ON_DOCK_TIME] to time:

TimeSerial([CARRIER_ON_DOCK_TIME]/100, [CARRIER_ON_DOCK_TIME] mod 100, 0)
 

Note that subtracting one date from another gives by default difference in days. To convert it to hours you may use Format() function

Format (TimeSerial([CARRIER_OFF_DOCK_TIME]/100, [CARRIER_OFF_DOCK_TIME] mod 100, 0) - TimeSerial([CARRIER_ON_DOCK_TIME]/100, [CARRIER_ON_DOCK_TIME] mod 100, 0), "h")
 

or

Format (TimeSerial([CARRIER_OFF_DOCK_TIME]/100, [CARRIER_OFF_DOCK_TIME] mod 100, 0) - TimeSerial([CARRIER_ON_DOCK_TIME]/100, [CARRIER_ON_DOCK_TIME] mod 100, 0), "hh:mm")
 

to get hours and minutes

or simply multiply the result on 24

(TimeSerial([CARRIER_OFF_DOCK_TIME]/100, [CARRIER_OFF_DOCK_TIME] mod 100, 0) - TimeSerial([CARRIER_ON_DOCK_TIME]/100, [CARRIER_ON_DOCK_TIME] mod 100, 0))*24
 

Regards,
Fish

[jrayjr]

Thanks Fish,
I will give it a try and post back.

-John

[jrayjr]

Thanks Fish, its work but I have to work with it alittle. Some of the values for the Total Time are incorrect but most are right.

Format (TimeSerial([CARRIER_OFF_DOCK_TIME]/100, [CARRIER_OFF_DOCK_TIME] mod 100, 0) - TimeSerial([CARRIER_ON_DOCK_TIME]/100, [CARRIER_ON_DOCK_TIME] mod 100, 0), "hh:mm")
 

Example:

1537 - 1658 = 02:21 is incorrect
2213 - 59 = 20:14 is incorrect
1411 - 1646 = 2:35 correct
601 - 1033 = 4:32 correct

So I may have to use conditional statements for when the time rolls into a new day too. If you have any ideas that would be great.

Thx,
John

[FishVal]

Hi, John.

The following code will work correctly.

Format (TimeSerial(int([CARRIER_OFF_DOCK_TIME]/100), [CARRIER_OFF_DOCK_TIME] mod 100, 0) - TimeSerial(int([CARRIER_ON_DOCK_TIME]/100), [CARRIER_ON_DOCK_TIME] mod 100, 0), "hh:mm")