Calculations using Time fields

  • Thread starter Thread starter GD
  • Start date Start date
G

GD

I am trying to set up a database (Access 2000) for time
sheet purposes. I have a field "RegTimeIn"
and "RegTimeOut" with an input mask of "09:00\ >L;_" for
each field. I am trying to set up a calculated field
called "TotalRegHours" in a query that will just give the
number of hours/minutes between the "RegTimeIn"
and "RegTimeOut". This field in return will be needed to
calculate the employee's total pay ("TotalRegHours"
* "RegRate").

I have tried to use the "HoursAndMinutes" function, but it
gives me an error saying that I have an undefined function
in my expression.

I have also tried setting this "TotalRegHours" field up in
a form and setting the control source to "=HoursAndMinutes
([RegTimeOut]-[RegTimeIn]) and it gives me a "#Name?"
error.

If I just set the calculated field up as TotalRegHours:
([RegTimeOut]-[RegTimeIn]), then my results come out in
some other number format. For example if
the "TotalRegHours" is one hour, my result is 0.04166. If
my "TotalRegHours" is one and a half hours, my result is
0.0625. Is there a way that I can format these figures
into "Hours" and "Minutes"?

Any advice on how to resolve this issue or any alternative
ways to get around this is greatly appreciated. Thank you
in advance for your help.
 
Your approach [RegTimeOut]-[RegTimeIn] is correct, only because the two
fields are date/time type the result is of the same type, so the number
returned is a fraction of a 24-hour day! To verify, if you multiply 0.04166
by 1440 (number of minutes in a day), you'll get 60 minutes.
To return the result in a standard time format, use the following expression
instead:
Format([RegTimeOut]-[RegTimeIn], "hh:nn:ss")

HTH,
Nikos
 
Back
Top