Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hours and Minutes

Hours and Minutes

Hours and Minutes

I have a field in my table that stores time...it is formatted for Short Time. The issue...this field shows how long it takes to do a task. Sometimes it takes over 24 hours. The field errors out if I enter more than 23:59. I would like the field to stay formatted to HH:MM but I need to exceed 24 hours.

Can anyone help?

RE: Hours and Minutes

Simple answer ... don't use a DateTime field for this. Such fields record a point in time and the Time portion is represented as a fraction of one day. Once you exceed one day then this data type rolls over to the next day.

You are attempting to record an elapsed time or time interval. Use a fieldtype like Long and store the elapsed time as minutes (assuming that you don't care about seconds). You can then easily format that in a query with

Format(Int(ElapsedTime/60), "00") & ":" & Format((ElapsedTime Mod 60), "00")

RE: Hours and Minutes

... or as a tiny variation on Golom's approach, depending on application, I might store the elapsed time in units of days (same as Access uses for DateTime), as a double, and format appropriately as Golom did. Disadvantage: the formatting needs rounding (I'd add a user-defined function so I don't need to type it out everywhere). Advantage: you can mix your elapsed time with genuine dates and the arithmetic will work correctly.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close