Calculating hours on a timeclock form
in [Queries]
|
From: joseph.johnson4 on 29 Mar 2010 13:41 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
From: J_Goddard via AccessMonster.com on 29 Mar 2010 17:46 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 joseph.johnson4 wrote: >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 http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: joseph.johnson4 on 30 Mar 2010 13:26 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 "J_Goddard via AccessMonster.com" wrote: > 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 > > > > > joseph.johnson4 wrote: > >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 > http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1 > > . >
From: J_Goddard via AccessMonster.com on 30 Mar 2010 14:02 Joe, How are you setting the values of the [TimeIn] and [TimeOut] fields (I assume it's through a form). What you need to do is to check what the values in those fields really are. Make a select query to look at the data in the table where these time fields are, and set the format of the time query fields to "dd mmm yyyy hh:nn" (without the quotes). Are the times what you expect them to be, i.e. not zeros? John joseph.johnson4 wrote: >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 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
From: PieterLinden via AccessMonster.com on 30 Mar 2010 14:09 joseph.johnson4 wrote: >John, > >I utilized the expression: >= datediff("m", [timeIn],[TimeOut])/60.0 > >That you suggested. In the HoursWorked field it just displays 0:00. I Joseph, "m" is for Months. "n" is for miNutes... just one of those fun gotchas if you don't read the fine print... -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
|
Next
|
Last
Pages: 1 2 3 Prev: Quirky Query Next: Expression for Criteria for Form with Combo Boxes |