Run Time Error 6 Overflow

[Big_Si]

Hi All

I am having problems with a module I created some time back. The script is used to calculate the number of working minutes between two dates and times. For the most part it works as it should however when the two times and dates seem to be more than a few months apart I get the error message "Run-Time Error '6' Overflow"

This is the module. Can somebody advise where I am going wrong and if possible offer a fix as

Option Compare Database
Option Explicit
 
Public Function NetWorkHours(dteStart As Date, dteEnd As Date) As Variant
 
Dim intGrossDays As Integer
Dim intGrossMins As Single
Dim dteCurrDate As Date
Dim i As Integer
Dim WorkDayStart As Date
Dim WorkDayEnd As Date
Dim nonWorkDays As Integer
Dim StartDayMins As Single
Dim EndDayMins As Single
Dim NetworkMins As Integer
 
NetworkMins = 0
nonWorkDays = 0
 
'Calculate workday hours on 1st and last day
WorkDayStart = DateValue(dteEnd) + TimeValue("08:00:00")
WorkDayEnd = DateValue(dteStart) + TimeValue("17:00:00")
StartDayMins = DateDiff("n", dteStart, WorkDayEnd)
EndDayMins = DateDiff("n", WorkDayStart, dteEnd)
'Calculate total hours and days between start and end times
intGrossDays = DateDiff("d", (dteStart), (dteEnd))
intGrossMins = DateDiff("n", (dteStart), (dteEnd))
'count number of weekend days and holidays involved
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 on same day
        NetworkMins = (intGrossMins - ((nonWorkDays) * 1440))
    Case 1
        'start and end on consecutive days
        NetworkMins = StartDayMins + EndDayMins
    Case Is > 1
        'start and end time on non-consecutive days
        NetworkMins = (((intGrossDays - 1) - nonWorkDays) * 480) + (StartDayMins + EndDayMins)
End Select
NetWorkHours = NetworkMins ' minutes only
End Function

 

[boblarson]

Change this:

Dim NetworkMins As Integer

to this:

Dim NetworkMins As LONG

 

[namliam]

Hmz, I seem to remember this function someplace

The problem probably lies in the Integer or Single variables.

read the help on them and find out why.... and what you need to do to fix it.

 

[Big_Si]

Thanks Guys!!!

Will have a read up for future reference but the change Bob mentioned seemed to do the trick for the here and now.

Thanks again!!