db slow down from function

[Lavey]

Hi, I have written a function to calculate the number of minutes elapsed between two dates (datediff) and also drop non working days (sat/sun + holidays).

The trouble is I use this function daily in queries to calcualte daily turnaround, but it is causing massive slow down on my PC. The PC is a p3 700 with 128 ram, is there anything that i can do to optimize my code (quite new tp coding - so there may be an easier way of doinf what i have done).

I have attached my code.....

Public Function NonWorkingDays(DateIn, DateOut)
On Error GoTo CalcError:

Dim TotalHours As Integer
Dim HoursToDrop As Integer
Dim StartDate As Date
Dim EndDate As Date
Dim Monday As Date
Dim Newstring As String
Dim DateCounter As Date
Dim HoliCount As Long
Dim HoliStart As Date
Dim HoliEnd As Date
Dim HoliDays As Long

StartDate = Format(DateIn, "general date&quot
EndDate = Format(DateOut, "general date&quot
TotalHours = DateDiff("n", StartDate, EndDate)
HoursToDrop = 0

'if received and completed on sat or sun then calc hours only
If WeekDay(StartDate) = vbSunday And WeekDay(EndDate) = vbSunday And TotalHours < 1440 Then
NonWorkingDays = Format(TotalHours / 60, &quot;0.0&quot
Exit Function
ElseIf WeekDay(StartDate) = vbSaturday And WeekDay(EndDate) = vbSaturday Or WeekDay(EndDate) = vbSunday And TotalHours < 2880 Then
NonWorkingDays = Format(TotalHours / 60, &quot;0.0&quot
Exit Function
Else
End If

'startdate convert to monday morning at 09:00 if received + completed on either Sat or Sun
If WeekDay(StartDate) = vbSunday Then
Newstring = StartDate + 1
Newstring = Left(Newstring, 10) & &quot; 08:00&quot;
Monday = Newstring
HoursToDrop = DateDiff(&quot;n&quot;, StartDate, Monday)
ElseIf WeekDay(StartDate) = vbSaturday Then
Newstring = StartDate + 2
Newstring = Left(Newstring, 10) & &quot; 08:00&quot;
Monday = Newstring
HoursToDrop = DateDiff(&quot;n&quot;, StartDate, Monday)
Else
End If

'drop all sat and sun between startdate and end date
If Newstring = &quot;&quot; Then
DateCounter = StartDate
Do While DateCounter < EndDate
If WeekDay(DateCounter) = vbSaturday Then
HoursToDrop = HoursToDrop + 1440
ElseIf WeekDay(DateCounter) = vbSunday Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop
Else
DateCounter = Newstring
Do While DateCounter < EndDate
If WeekDay(DateCounter) = vbSaturday Then
HoursToDrop = HoursToDrop + 1440
ElseIf WeekDay(DateCounter) = vbSunday Then
HoursToDrop = HoursToDrop + 1440
End If
DateCounter = DateCounter + 1
Loop
End If

'check for holidays in the start and end date period (based on the holidays table)then convert to hours to drop
HoliCount = 0
HoliStart = Left(StartDate, 10)
HoliEnd = Left(EndDate, 10)
Do While HoliStart < HoliEnd
If WeekDay(HoliStart) = vbSaturday Or WeekDay(HoliStart) = vbSunday Then
Else
HoliDays = DCount(&quot;holidate&quot;, &quot;HolidayTable&quot;, &quot;holidate = #&quot; & HoliStart & &quot;#&quot
HoliCount = HoliCount + HoliDays
End If
HoliStart = HoliStart + 1
Loop
HoliCount = HoliCount * 1440
HoursToDrop = HoursToDrop + HoliCount

'true working hours is total hours minus the hours to be dropped - and there you go !

NonWorkingDays = TotalHours - HoursToDrop
NonWorkingDays = Format(NonWorkingDays / 60, &quot;0.0&quot
Exit Function


CalcError:
If Err.number = 94 Then
NonWorkingDays = Null
End If
End Function


Any ideas wuold be very helpful thanks......

 

[JimHorton]

Lavey,
I didn't go through all the code to look for ways to shorten or speed it up, but in general, you should have the function be set to the type it returns, else it's a Variant, an instant slowdown, ie NonWorkingDays(x,x) as single (or whatever you wish to return)

Also, you have DateIn and DateOut as args, but then immediately assign them to other variables. If you're changing them in the code, and don't want to change them in the caller, then use NonWorkingDays(ByVal DateIn as date,ByVal DateOut as date) as single--this will save a little bit also. You don't need to Format() in order to use the date functions, like Weekday, etc., if datein and dateout are dates to begin with; the Left(StartDate,10) shouldn't be needed either, you can strip the time by using cdate(clng(date)). I'm not sure if that itself is fater than the Left(xx), but you don't need to worry about formatting this way.
--Jim

 

[Eldaria]

Another tip is to use &quot;Select Case&quot;, instead of &quot;If&quot;
Eldaria

http://www.eldaria.net

That was my 25cent** of opinion.

** Inclusive Intrest, tax on interest, Genral tax, Enviromental tax, Tax, and tax on intrest, tax on fees, tax on tax, and other Various taxes and fees.