Date Calculation Function returning #ERROR in query

[BRASILO]

I'm using the following code. I'm trying to use this sample to help me calculate hours and minutes. However, some fields maybe blank and it's throwing an error. Can someone help me with this?

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(Nz(dteEnd, 0)) + TimeValue("12:00:00am")
'WorkDayend = DateValue(Nz(dteStart, 0)) + TimeValue("11:59:59pm")
WorkDayStart = DateValue(dteEnd) + TimeValue("12:00:00am")
WorkDayend = DateValue(dteStart) + TimeValue("11:59:59pm")
StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)

'Calculate total hours and days between start and end times
intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossHours = DateDiff("n", (dteStart), (dteEnd))
'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("[HolidayDate]", "TBL_HOLIDAYS", "[HolidayDate] = #" & 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

[ranman256]

where is the error?
put a stop point at nonWorkDays=0
then step thru 1 line at a time (f8) then hover over the variable to see the value. (and where it goes wrong)

[BRASILO]

where is the error?
put a stop point at nonWorkDays=0
then step thru 1 line at a time (f8) then hover over the variable to see the value. (and where it goes wrong)

Thank you for the quick response. I tried that. My query is still throwing an #ERROR and I can't find it where on the code it's doing this. It not all the fields. its when it compares a field that has a date and one that is blank.

Any suggestions? Here is my query code: Expr1: NetWorkhours([[mainDate],[acTIME])

[ranman256]

are you talking field in a query the uses this function? you have to code for null exceptions, since you cannot do math on null dates.

[BRASILO]

here are no fields here in the code...its all variables.
are you talking field in a query the uses this function? you have to code for null exceptions, since you cannot do math on null dates.

I tried adjusting the code using IsNumeric, Isdate, Cdate, IsError. and I can't figure out where I'm going wrong.

[BRASILO]

are you talking field in a query the uses this function? you have to code for null exceptions, since you cannot do math on null dates.

Yes, I'm using the function in a query.

[BRASILO]

are you talking field in a query the uses this function? you have to code for null exceptions, since you cannot do math on null dates.

Also, tried using this catch:

   If IsNull(dteStart) Then Exit Function
   If IsNull(dteEnd) Then Exit Function
   If Not (IsDate(dteStart)) Then Exit Function
   If Not (IsDate(dteEnd)) Then Exit Function

[ssanfu]

I see a couple of potential errors in calculations in your code.

Would you provide some examples of time periods (start and end date/times) and the expected hours?

For example, say you started work at 8:00 am on 5 March 2018 (start date) and end date is 5:00pm on 9 March 2018. How many hours should be returned?
I would contend the hours should be 40, based on an 8 hour work day. But your code returns 2003.....


Also show some examples where one value for start or End is NULL. What would you want to be returned? 0? Null?

[BRASILO]

I see a couple of potential errors in calculations in your code.

Would you provide some examples of time periods (start and end date/times) and the expected hours?

For example, say you started work at 8:00 am on 5 March 2018 (start date) and end date is 5:00pm on 9 March 2018. How many hours should be returned?
I would contend the hours should be 40, based on an 8 hour work day. But your code returns 2003.....


Also show some examples where one value for start or End is NULL. What would you want to be returned? 0? Null?

Hello, thank you for assisting me.

I'm using a 24hr work schedule. However, in the code above it allows you to adjust the hours based on entry. Currently it's at START: 12AM END: 11:59PM. I'd like it to return 0 and not null. Because in my query I need it to display a number.

[June7]

Only Variant type variable can hold Null, therefore testing for Null in the function is useless, never gets that far since Null cannot be passed to non-variant type.

Don't call the function if field is null. In query:

IIf([StartDate] Is Null Or [EndDate] Is Null, Null, NetWorkhours([StartDate], [EndDate]))

[BRASILO]

Only Variant type variable can hold Null, therefore testing for Null in the function is useless, never gets that far since Null cannot be passed to non-variant type.

Don't call the function if field is null. In query:

IIf([StartDate] Is Null Or [EndDate] Is Null, Null, NetWorkhours([StartDate], [EndDate]))

I tried using you suggestions in the query. However, it still produces #ERROR. Also, I was hoping to try to get some help modifying to the code to catch the null / 0 values. so I do not get a #ERROR.

[June7]

Did you post the complete procedure? Don't see End Function line in the code.

You should use indentation in code to make it more readable.

I added the End Function line and disabled code checking the holiday table because I don't want to build one. Called function in immediate window:

?NetWorkhours(#3/20/2018 8:00:00 AM#,#3/20/2018 4:00:00 PM#)
480

As you can see, something very wrong.

I also tested the IIf() in query. There is no error returned even when field is null.

[ssanfu]

In Post #8, I asked

Would you provide some examples of time periods (start and end date/times) and the expected hours?

For example, say you started work at 8:00 am on 5 March 2018 (start date) and end date is 5:00pm on 9 March 2018. How many hours should be returned?
I would contend the hours should be 40, based on an 8 hour work day. But your code returns 2003.....


Also show some examples where one value for start or End is NULL. What would you want to be returned? 0? Null?

StartDayhours = DateDiff("n", dteStart, WorkDayend)
EndDayhours = DateDiff("n", WorkDayStart, dteEnd)

In the DateDiff calculations in your code, the "n" is for MINUTES. If you want hours, you should be using "h" (for hours).

[BRASILO]

Did you post the complete procedure? Don't see End Function line in the code.

You should use indentation in code to make it more readable.

I added the End Function line and disabled code checking the holiday table because I don't want to build one. Called function in immediate window:

?NetWorkhours(#3/20/2018 8:00:00 AM#,#3/20/2018 4:00:00 PM#)
480

As you can see, something very wrong.

I also tested the IIf() in query. There is no error returned even when field is null.

Thank you!! I'll try again and let you guys know what happens.

[BRASILO]

Did you post the complete procedure? Don't see End Function line in the code.

You should use indentation in code to make it more readable.

I added the End Function line and disabled code checking the holiday table because I don't want to build one. Called function in immediate window:

?NetWorkhours(#3/20/2018 8:00:00 AM#,#3/20/2018 4:00:00 PM#)
480

As you can see, something very wrong.

I also tested the IIf() in query. There is no error returned even when field is null.

I found the mistake I was making. The query suggestion you gave earlier works. Thanks you