ok Anjie here goes nothing
This comes from a complex work hours calculation that I have reduce to bare bones for your requirement
You will need a form or some method to supply the query with an id for your staff member
copy into your query - hourstot in one column and minswrkd in another
hourstot: hrswrkd([start_date],[start_time],[end_date],[end_time],"h",
[TblStaffMember]![intstaffid])
minswrkd: hrswrkd([start_date],[start_time],[end_date],[end_time],"m",
[TblStaffMember]![intstaffid])
replace the blue text with your own variable for staff id
NB you are calling the same function I know but note the
"h" and
"m"
this becomes Retvalue in the function meaning returned value
h= hours
m = mins
b1:=IIf([break1]=Yes,"15","0")
b2:=IIf([break2]=Yes,"15","0")
LB:IIf([lunchbreak]=Yes,"60","0")
totMinsBrk:sum(cint(b1)+ cint(b2)+ cint(lunchbreak))
totminswrkd: hourstot*60 + minswrkd - totMinsBrk
PaidHrs: totminswrkd /60
paidmins:totminswrkd mod 60
Error trapping has been removed to maintain uniformity in your code ( so you can use your own in short)
next create a new module BasCaclHrs
copy this vba into it
after the option explicit
Public Type TimeParts
Dy As Integer
Hr As Integer
mins As Integer
Secnds As Integer
End Type
you need to copy the following functions
[ul]
[li]hrsWrkd [/li]
[li]GetElapsedTime[/li]
[li]cvstrTodate[/li]
[/ul]
Code:
Function hrsWrkd(S_date, s_time, e_date, e_time, RetValue As String, id) As Integer
'Exit Function
If IsNull(S_date) Then hrsWrkd = 0: Exit Function
If IsNull(e_date) Then hrsWrkd = 0: Exit Function
If IsNull(s_time) Then hrsWrkd = 0: Exit Function
If IsNull(e_time) Then hrsWrkd = 0: Exit Function 'these records have their values set to zero so they can be identified as records requiring further input.
Dim ENDTIME As Date
Dim Startime As Date
Dim startStr As String
Dim EndStr As String
Dim DyHours As Integer
Dim Hrs As Integer
Dim minits As Integer
Dim secnd As Integer
Dim returnedhrs As TimeParts
'as the times are delivered to the function as strings - they have to be converted to times
s_time = cvstrTodate(s_time)
e_time = cvstrTodate(e_time)
startStr = CStr(S_date & " " & s_time)
EndStr = CStr(e_date & " " & e_time)
Startime = Format(CVDate(startStr), "dd/mm/yyyy hh:nn:ss")
ENDTIME = Format(CVDate(EndStr), "dd/mm/yyyy hh:nn:ss")
returnedhrs = GetElapsedTime(ENDTIME - Startime)
''debug.Print id
DyHours = returnedhrs.Dy * 24
Hrs = returnedhrs.Hr
If RetValue = "h" Then hrsWrkd = DyHours + Hrs
If RetValue = "m" Then hrsWrkd = returnedhrs.mins
End Function
Code:
Function GetElapsedTime(interval) As TimeParts
Dim Totalhours As Long, totalminutes As Long, totalseconds As Long
Dim days As Long, hours As Long, Minutes As Long, Seconds As Long
days = Int(CSng(interval))
Totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
totalseconds = Int(CSng(interval * 86400))
hours = Totalhours Mod 24
Minutes = totalminutes Mod 60
Seconds = totalseconds Mod 60
' GetElapsedTime = days & " Days " & hours & " Hours " & Minutes & " Minutes " & Seconds & " Seconds "
GetElapsedTime.Dy = days
GetElapsedTime.Hr = hours
GetElapsedTime.mins = Minutes
GetElapsedTime.Secnds = Seconds
End Function
& finally
Code:
Function cvstrTodate(TM)
Dim stime As Date
stime = Format(Left(TM, 2) & ":" & Right(TM, 2), "short time")
cvstrTodate = stime
End Function
You now have hours and mins calculated in your query
hope this helps...
happy easter
jo
