Calculating hours on a timeclock form

[joseph.johnson4]

I am trying to calculate the number of hours worked utilizing the "TimeIn"
and "TimeOut" fileds on a subform that will calculate the total number of
hours per week. The expression that I am utilizing is:

=HoursAndMinutes([TimeOut]-[TimeIn])

I have also created an HoursandMinutes which is:

ublic Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

The error I keep coming up with is #Name?

I need the form to do three things:

1) Calculate the total hours worked each day
2) Then calculate a summed total of the hours worked per week
3) Lastly, keep a running total of the hours each employee works as long as
they are employed.

I could really use some assistance and help from those that have more
experience with Access 2007 than I do.

Thanks, Joe

 

[joseph.johnson4]

John,

I utilized the expression:
= datediff("m", [timeIn],[TimeOut])/60.0

That you suggested. In the HoursWorked field it just displays 0:00. I
checked the fields for TimeIn and TimeOut and they are both set to the
Date/Time type. I know that the expression that you provided me is taking me
in the right direction, but I am just not there yet. Any suggests. I do
appreciate your helping me with this problem. I will also try paying around
with the DateDiff function and see what I can come up with.

Thanks,

Joe

Hi -

Take a look at the DateDiff function, which returns the difference between
two times in whatever units you specify, in this case hours.

But, what are the data types of TimeOut and TimeIn, i.e. how are they being
entered on the subform, and what is their data type in the database table?
Are they Date/time type? If not, some your expressions may not work, in fact
probably won't.

Another observation - your function is returning a string representing the
difference in times, but you say you are doing calculations with the data -
you can't do math with strings. Perhaps you should be using decimal hours
for this, for example, something like HoursWorked = datediff("m", [timeIn],
[TimeOut])/60.0

But the important part is what TimeIn and TimeOut are.

HTH

John

I am trying to calculate the number of hours worked utilizing the "TimeIn"
and "TimeOut" fileds on a subform that will calculate the total number of
hours per week. The expression that I am utilizing is:

=HoursAndMinutes([TimeOut]-[TimeIn])

I have also created an HoursandMinutes which is:

ublic Function HoursAndMinutes(interval As Variant) As String
'***********************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'***********************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long
If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))

totalminutes = Int(CSng(interval * 1440)) ' 1440 = 24 hrs * 60 mins
minutes = totalminutes Mod 60

totalseconds = Int(CSng(interval * 86400)) ' 86400 = 1440 * 60 secs
seconds = totalseconds Mod 60

If seconds > 30 Then minutes = minutes + 1 ' round up the minutes and
If minutes > 59 Then hours = hours + 1: minutes = 0 ' adjust hours

HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

The error I keep coming up with is #Name?

I need the form to do three things:

1) Calculate the total hours worked each day
2) Then calculate a summed total of the hours worked per week
3) Lastly, keep a running total of the hours each employee works as long as
they are employed.

I could really use some assistance and help from those that have more
experience with Access 2007 than I do.

Thanks, Joe

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.

 

[joseph.johnson4]

John,

The subform is designed where it has a combo box that allows you to select
the Day of the Week, then a date field, then TimeIn field, then TimeOut
field, and lastly HoursWorked for the day. The TimeIN and TimeOut fields are
formatted to medium time. I changed the expression as you had instructed, but
it gives me really strange totals like: 3:01 PM TimeIn, 11:08 PM TimeOut =
2:48. I know in one of your posts you suggested changing the TimeIn and
TimeOut format to "dd mmm yyyy hh:nn" without the quotations. I tried with
the specified format, but still ended up with the same problem.

Joe

Joe -

I mis-typed the DateDiff - it should be

= datediff("n", [timeIn],[TimeOut])/60.0, using "n" to give you minutes. "m"
gives the difference in months.

Sorry 'bout that!!

John

John,

I utilized the expression:
= datediff("m", [timeIn],[TimeOut])/60.0

That you suggested. In the HoursWorked field it just displays 0:00. I
checked the fields for TimeIn and TimeOut and they are both set to the
Date/Time type. I know that the expression that you provided me is taking me
in the right direction, but I am just not there yet. Any suggests. I do
appreciate your helping me with this problem. I will also try paying around
with the DateDiff function and see what I can come up with.

Thanks,

Joe

Hi -

[quoted text clipped - 62 lines]

Thanks, Joe

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca



.

 

[John W. Vinson]

The subform is designed where it has a combo box that allows you to select
the Day of the Week, then a date field, then TimeIn field, then TimeOut
field, and lastly HoursWorked for the day. The TimeIN and TimeOut fields are
formatted to medium time. I changed the expression as you had instructed, but
it gives me really strange totals like: 3:01 PM TimeIn, 11:08 PM TimeOut =
2:48. I know in one of your posts you suggested changing the TimeIn and
TimeOut format to "dd mmm yyyy hh:nn" without the quotations. I tried with
the specified format, but still ended up with the same problem.

Just note that the Format of a textbox or a field is *absolutely irrelevant*
to this problem.

A date is not stored as a text string, but as a Double Float count of days and
fractions of a day since midnight, December 30, 1899. A time-only date/time
field is just a time on that long-ago day - e.g. if you store 8:00am it's
actually STORED as 0.333333333333333333333, but can be displayed any way you
like - "8:00am" or "12/30/1899 08:00:00" or whatever.

The calculations work on the stored value, not on the format.

 

[joseph.johnson4]

John,

I was able to get the calculations that I needed for HoursWorked utilizing
the expression "= [TimeOut] - [TimeIn]" using the Short Tiem format.

I have been working on trying to get the TimeClock Subform to sum the
HoursWorked per week utilizing the expression "= SUM(HoursWorked)". However,
I just keep getting a syntax error. I am also trying to create an expresiion
that keeps a running total of hours worked labelled TotalHours, but again I
am having the same syntax error when I utilize the expression "=
SUM(WeeklyHours)".

In the end I will need to utilize the Sum of WeeklyHours and TotalHours to
calculate the gross weekly pay and YTD pay.

I have greatly appreciated your assistance and could use some more
suggestions or ideas.

Thanks, Joe

 

[John W. Vinson]

John,

I was able to get the calculations that I needed for HoursWorked utilizing
the expression "= [TimeOut] - [TimeIn]" using the Short Tiem format.

This will work... but ONLY for durations under 24 hours.

I have been working on trying to get the TimeClock Subform to sum the
HoursWorked per week utilizing the expression "= SUM(HoursWorked)". However,
I just keep getting a syntax error. I am also trying to create an expresiion
that keeps a running total of hours worked labelled TotalHours, but again I
am having the same syntax error when I utilize the expression "=
SUM(WeeklyHours)".

Again, a Date/Time field is designed to store a specific point in time - NOT a
duration. You should calculate the hours worked using the DateDiff function,
to calculate minutes worked and divide by 60 (otherwise it will calculate the
time in integer hours):

HoursWorked: DateDiff("n", [TimeIn], [TimeOut])/60.

This will get you a value such as 7.84 or 8.056, not an hours:minutes
appearance, though you can use expressions to generate that appearance.

In the end I will need to utilize the Sum of WeeklyHours and TotalHours to
calculate the gross weekly pay and YTD pay.

The hours and fractional hours will let you do this. A Date/Time value such as
[TimeOut] - [TimeIn] will NOT let you do so.

 

[joseph.johnson4]

I utilized the HoursWorked: =DateDiff("n",[TimeIn],[TimeOut])/60 expression
as you had suggested and definitely see what you were talking about, in
regards, to utilizing the expression that I was using. The results were as
you had stated once I changed the formats to reflect a general number. Now I
have that aspect of the calculation taken care of on the TimeClock Subform
that I have created.

The problem that I am now experiencing is when it comes to the expression
for the WeeklyHoursTotal, which I have been utilizing the expression
=DSum([HoursWorked],"Schedule"), but all I keep getting in the box where the
total should appear is #Error. I have aslo tried the Sum fubction with the
same problem. I feel like I am overlooking something simple in order to get
it working, but I am just missing it.

I have appreciated your help and assistance with this particular problem.

John,

I was able to get the calculations that I needed for HoursWorked utilizing
the expression "= [TimeOut] - [TimeIn]" using the Short Tiem format.

This will work... but ONLY for durations under 24 hours.

I have been working on trying to get the TimeClock Subform to sum the
HoursWorked per week utilizing the expression "= SUM(HoursWorked)". However,
I just keep getting a syntax error. I am also trying to create an expresiion
that keeps a running total of hours worked labelled TotalHours, but again I
am having the same syntax error when I utilize the expression "=
SUM(WeeklyHours)".

Again, a Date/Time field is designed to store a specific point in time - NOT a
duration. You should calculate the hours worked using the DateDiff function,
to calculate minutes worked and divide by 60 (otherwise it will calculate the
time in integer hours):

HoursWorked: DateDiff("n", [TimeIn], [TimeOut])/60.

This will get you a value such as 7.84 or 8.056, not an hours:minutes
appearance, though you can use expressions to generate that appearance.

In the end I will need to utilize the Sum of WeeklyHours and TotalHours to
calculate the gross weekly pay and YTD pay.

The hours and fractional hours will let you do this. A Date/Time value such as
[TimeOut] - [TimeIn] will NOT let you do so.

 

[John W. Vinson]

The problem that I am now experiencing is when it comes to the expression
for the WeeklyHoursTotal, which I have been utilizing the expression
=DSum([HoursWorked],"Schedule"), but all I keep getting in the box where the
total should appear is #Error. I have aslo tried the Sum fubction with the
same problem. I feel like I am overlooking something simple in order to get
it working, but I am just missing it.

You can't reuse a calculated field in a further calculation. You'll need to
recapitulate the calculation in the expression you're summing.