Help understanding code to run query

[radguy]

I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.

I need this time to be calculated in hours.

In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.

At least if i could get the working days i could then convert it into hours.

I found this code:

Option Compare Database


Public Function NetWorkhours(dteStart As Date, dteEnd As Date) As Single


Dim intGrossDays As Integer
Dim intGrossHours As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayend As Date
Dim nonWorkDays As Integer
Dim StartDayhours As Single
Dim EndDayhours As Single


NetWorkhours = 0
nonWorkDays = 0
'Calculate work day hours on 1st and last day


WorkDayStart = DateValue(Rebuild_Start_Date_Time) + TimeValue("06:00am")
WorkDayend = DateValue(Rebuild_Complete_Date_Time) + TimeValue("02:00pm")
StartDayhours = DateDiff("n", Rebuild_Start_Date_Time, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, Rebuild_Complete_Date_Time)


'adjust for time entries outside of business hours


If StartDayhours < 0 Then
StartDayhours = 0
End If
If EndDayhours > 8 Then
EndDayhours = 8
End If


'Calculate total hours and days between start and end times


intGrossDays = DateDiff("d", Rebuild_Start_Date_Time, Rebuild_Complete_Date_Time)
intGrossHours = DateDiff("n", Rebuild_Start_Date_Time, Rebuild_Complete_Date_Time)


'count number of weekend days and holidays (from a table called "Holidays" that lists them)
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbSaturday) < 3 Then
nonWorkDays = nonWorkDays + 1
Else
If Not IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & Int(dteCurrDate) & "#")) Then
nonWorkDays = nonWorkDays + 1
End If
End If
Next i


'Calculate number of work hours


Select Case intGrossDays
Case 0
'start and end time on same day
NetWorkhours = intGrossHours
Case 1
'start and end time on consecutive days
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours
Case Is > 1
'start and end time on non consecutive days
NetWorkhours = NetWorkhours - (nonWorkDays * 1)
NetWorkhours = (intGrossDays - 1 - nonWorkDays) * 8
NetWorkhours = NetWorkhours + StartDayhours
NetWorkhours = NetWorkhours + EndDayhours


End Select

In my query I typed: workinghours(([Replacement Event]![Date/Time Start Rebuild],[Replacement Event]![Date/Time Complete Rebuild])/60)

however when I tried to run the query I got the error message:

Syntax error (comma) in query expression 'workinghours(([Replacement Event]![Date/Time Start Rebuild],[Replacement Event]![Date/Time Complete Rebuild])/60)'

How can I get this query to run?

Can anyone help?

[ItsMe]

Cross Posted
https://www.accessforums.net/access/...run-45753.html