Help with understanding code and getting query to run

[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]

I did not review the function you pasted here but you would call it like this.

NetWorkhours(01 - 01 - 2013, 01 - 01 - 2014)

I believe that is the correct format to assign to a variable type Date.

What you can do is assign dates to a variable behind a form and then place your declared variables in the function call

Dim dtStart as date
Dim dtEnd as date
Dim sglResult as single

dtStart = Me.TextBoxOne
dtEnd = Me.TextBoxTwo

sglResult = NetWorkhours(dtStart, dtEnd)
msgbox sglResult

[radguy]

I am new to Access so please bare with me.

will assigning a date to a variable allow me to store (or see in Query) the networkhours between the two dates or will it only display the networkhours in a message box?

I have also made the changes and this is what is showing:




Can you help with this?

[ItsMe]

...I have also made the changes and this is what is showing:

What changes did you make? Can you post the code? Please use the Go Advanced option when posting here in the forum. There is a tool there that you can use to wrap code in Tags to preserve formatting (#).

[John_G]

Hi -

Going back to your original post - the error is I think a mis-placed opening bracket:

workinghours(([Replacement Event]![Date/Time Start Rebuild],[Replacement Event]![Date/Time Complete Rebuild])/60)

should be

(workinghours([Replacement Event]![Date/Time Start Rebuild],[Replacement Event]![Date/Time Complete Rebuild])/60)

HTH

John

[radguy]

What changes did you make? Can you post the code? Please use the Go Advanced option when posting here in the forum. There is a tool there that you can use to wrap code in Tags to preserve formatting (#).

I've changed the input value for the Query so that it reads Networkinghours(([Replacement Event]![Date/Time Start Rebuild],[Replacement Event]![Date/Time Complete Rebuild])/60). I am not sure how to put the changes you suggested into the code so that it can run.

Here is the 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
End Function

[ItsMe]

I missed the part about the Query Criteria earlier. So,

... ]![Date/Time Complete Rebuild])/60 ...

Is not going to work with the function you have. The parameters are set to accept Data Type Date. You cannot just divide a date, or a number, by 60 and pass it to your function.

I hope this sheds some light as to why the error and why post #2 offers examples in VBA how to test the function.

[radguy]

This is the query that I put into my database: NetWorkhours([Replacement Event]![Date/Time Start Rebuild],[Replacement Event]![Date/Time Complete Rebuild])

It still gives me the error that I posted in post #3.

How should I incorporate post #2 with the code that I am using in order to make it work?

Sorry for all the questions, but I am new to this.

[John_G]

NetWorkhours(01 - 01 - 2013, 01 - 01 - 2014)

I believe that is the correct format to assign to a variable type Date.

No, that's not correct for dates. Try:

NetWorkhours(#01/01/2013#, #01/01/2014")

Dates used in this context use mm/dd/yyyy format.

John

[John_G]

Going back to your post #3 -

What is in the form field or variable Rebuild_Start_Date_Time? The DateValue function expects a string that can be interpreted as a valid date, otherwise you will get that Type Mismatch error.

John

[radguy]

Going back to your post #3 -

What is in the form field or variable Rebuild_Start_Date_Time? The DateValue function expects a string that can be interpreted as a valid date, otherwise you will get that Type Mismatch error.

John

Attached is the screen shot for the table n design view.



Here is the form in Design View:

[John_G]

I think it's a typo. Going back to post #3, the line with the error has Rebuild_Start_Date_Time.

Your form control is called Rebuild Start Date/Time, so your code should be using that.

Here's a suggestion:

In EVERY module of code, include Option Explicit following Option Compare Database.

This forces you to explicitly declare all variables with a Dim statement.

Then, after ANY coding change, compile your code (Debug - Compile in the VBA coding window).

You would be amazed at how many mistakes this will catch, and I'm almost certain it would have caught this one.

John

[radguy]

I have compiled my code and this is what occurs:



How do I declare my variable?

Is it Dim Rebuild_Start_Date_Time Double


​Will it also still be linked to the field that I am trying to reference?

[ItsMe]

I took a look at your function and I do not see it working because Rebuild_Start_Date_Time is not defined anywhere. It seems to be a constant or something that was declared somewhere and now is missing. It should be dimensioned as a String and the value should be a date.

[radguy]

Thanks for your help John. I am now able to run the code.

I have one more question, toward the bottom it says that I should create a separate table labeled "Holidays." There I can list out all of the days that I would like to be excluded from the calculation of time between two points. Do I have the data type correct as a text or should it be date & time?

Attached are two screen shots of the table:



This is my code:

Option Compare Database
Option Explicit


Public Function NetWorkhours(Rebuild_Start_Date_Time As Date, Rebuild_Complete_Date_Time 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 = Rebuild_Start_Date_Time + 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
End Function