How do I add times beyond 24 hours in Access?

D

DavidDonaldDoo

I need to create a database that can add hours worked in an [hh:mm] format
that can surpass 24 hours. I do not know which format to choose when setting
up the field.

Thanks
 
R

Rick Brandt

DavidDonaldDoo said:
I need to create a database that can add hours worked in an [hh:mm] format
that can surpass 24 hours. I do not know which format to choose when setting
up the field.
Click to expand...

Access (actually no database that I know of) has such a type of field. To you
and I and even to a program like Excel the expression 4:32 can mean either the
time 4:32 or the duration 4 hours and 32 minutes. To Access it ONLY means the
time of day 4:32. Since DateTimes are stored internally as numbers you can do
"DateTime math", but as you have seen, doing this with time really only works as
long as the total stays under 24 hours.

The usual practice is to store the smallest interval you care about as Integers
or LongIntegers and then use an expression after doing the summing that
*displays* the result in HH:MM format.

Another way to go is to store start and end DateTimes and then use DateDiff() to
return the total Hours or Minutes or Seconds between them. This result can
again be used in an expression for a result in HH:MM format.
 
J

John Vinson

I need to create a database that can add hours worked in an [hh:mm] format
that can surpass 24 hours. I do not know which format to choose when setting
up the field.

Thanks
Click to expand...

You can't.

Access Date/Time values don't have a format that does this, and
they're really not suitable for storing durations. I'd suggest storing
the duration in a Long Integer field storing minutes (e.g. 25 hours =
1500) and displaying the results with an expression like

[Duration] \ 60 & Format([Duration] MOD 60, ":00")

John W. Vinson[MVP]
 
D

DavidDonaldDoo

Thanks
I know that in excel you can customize the format with [hh]:mm:ss and this
allow the function. I was hoping for a similar solution in Access. Seems odd
not to have this function. It's just a base 60 counting.

I appreciate your response.

D

Rick Brandt said:
DavidDonaldDoo said:
I need to create a database that can add hours worked in an [hh:mm] format
that can surpass 24 hours. I do not know which format to choose when setting
up the field.
Click to expand...

Access (actually no database that I know of) has such a type of field. To you
and I and even to a program like Excel the expression 4:32 can mean either the
time 4:32 or the duration 4 hours and 32 minutes. To Access it ONLY means the
time of day 4:32. Since DateTimes are stored internally as numbers you can do
"DateTime math", but as you have seen, doing this with time really only works as
long as the total stays under 24 hours.

The usual practice is to store the smallest interval you care about as Integers
or LongIntegers and then use an expression after doing the summing that
*displays* the result in HH:MM format.

Another way to go is to store start and end DateTimes and then use DateDiff() to
return the total Hours or Minutes or Seconds between them. This result can
again be used in an expression for a result in HH:MM format.
Click to expand...
 
Top