Calculating Hours, passing in a time format

G

Guest

I know very similar questions have been asked, and I searched and read
through all the links...my answer is probably somewhere in there but I must
be missing it. Please help!

I have a form that needs to keep a running sum of hours (including minutes)
used. Client starts out with a set number of hours and hours:minutes are
subtracted as they are used. Right now the field is a number data type
because access will not let the user enter an hour format over 24 hrs. For
example, client has 100 unused hours, I need to subtract 50:45 (fifty hours
and 45 minutes) for a resulting total of 49:15.

Users will need to enter hours in the format hours:minutes. Any
suggestions would be very appreciated.
 
S

Steve Schapel

Sandie,

If you need to enter the time used in the hours:minutes syntax, I think
you will need it to be a Text data type field. Otherwise, it would be
better to have 2 separate number data type fields, for hours and
minutes. The data entry via your form could be designed so the controls
bound to these 2 fields are right nect to each other, with a ":" label
between them, if the appearance of hours:minutes is important... would
that be possible?

Either way, I think it is easiest to work your calculations in munutes
and then converting back to hours/minutes. For example...
HoursRemaining:
(([AllocatedHours]*60+[AllocatedMinutes])-([UsedHours]*60+[UsedMinutes]))\60
MinutesRemaining:
(([AllocatedHours]*60+[AllocatedMinutes])-([UsedHours]*60+[UsedMinutes]))
Mod 60

If you are using the text field, you will first have to separate the
hours and minutes components by using a string manupulation function.
For example...
HoursUsed: Val(Left([TimeUsed],InStr([TimeUsed],":")-1))

When you decide which way you want to do it, you could write a
User-Defined Function to work this out, to simplify your
queries/expressions.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top