Field Math

  • Thread starter Thread starter Zanstemic
  • Start date Start date
Z

Zanstemic

I have "From Time", "To Time" and a "Total Time" field.

I'm looking for the recommended approach to total these time. I've tried to
add it in a query however, addition (+) gives me a total of both time instead
of the amount of time between the start and stop.

For example: 8:00am to 9:00am should total 1 hour. Instead it's giving me
5:00PM

I also have a Total Time in the table but not sure if it's needed.

Any suggestions are appreciated.
 
I have "From Time", "To Time" and a "Total Time" field.

I'm looking for the recommended approach to total these time. I've tried to
add it in a query however, addition (+) gives me a total of both time instead
of the amount of time between the start and stop.

For example: 8:00am to 9:00am should total 1 hour. Instead it's giving me
5:00PM

I also have a Total Time in the table but not sure if it's needed.

Any suggestions are appreciated.
Click to expand...

Since Access stores date and time values as a Double datatype number,
if you use + to add them that's what you will get, addition.
8 + 9 = 17. Formated as DateTime, 17:00 hours is 5 PM.

What you want is the DIFFERENCE between 2 DateTime values, so you need
to subtract the earlier value from the later value.
Look up the DateDiff function in VBA help.

ElapsedTime:DateDiff("n",[FromTime],[ToTime])

The answer will be in minutes.

Note... I've changed the column name to more accurately reflect the
result, the time elapsed between 2 fields.

Also note... Using 8 AM and 9 AM as the values, the elapsed value of
60 (or 1 if you use "h" for hours) is not a time of day but a number.
Do not format it as a time value.
 
Back
Top