workdays function issue

[Kevin Wilcox]

I'm using a workdays function I picked up from one of the access forums, and have encountered a strange issue. When using this within a query and setting criteria, i.e. > 2 or = 4 etc, it works fine so long as I don't have criteria for other fields that are e.g. <> X AND <> Y. It's fine if the criteria for the other fields are e.g. = X. With the former, it returns an error message saying datatype mismatch. Incidentally, the exact function is not important, I've tried 2 or 3 different workday functions as well as one I wrote myself, and all return the same error. What's odd is that if I apply the sql equivalent of the any of these functions, the query works fine. Any answers?

[FishVal]

Hi, Kevin.

Could you post the function code or link to the source?
It seems that just changing the function returning value type to Variant may solve the problem.

[Kevin Wilcox]

Hi FishVal, here are three that I used, all with the same result. I think I did play around the data type though couldn't swear to it.

Option Compare Database
Option Explicit
'not used in programme, causes problems when using with criteria in filtered events "all other breaches"
'... but sql equivalent does work.
Public Function fWeekends(StartDate As Date) As Integer
 
Select Case Weekday(StartDate)
Case 5
fWeekends = (Date - StartDate) - 3
Case 6
fWeekends = (Date - StartDate) - 2
Case Else
fWeekends = (Date - StartDate) - 1
End Select
 
End Function
Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
On Error GoTo Err_fWorkDays
 
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
 
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
 
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
 
intCount = 0
 
Do While StartDate <= EndDate
 
If Not IsNull(StartDate) And Not IsNull(EndDate) Then
    rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
        If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
            If rst.NoMatch Then
            intCount = intCount + 1
            End If
        End If
End If
 
StartDate = StartDate + 1
 
Loop
 
fWorkDays = intCount
 
Exit_fWorkDays:
Exit Function
 
Err_fWorkDays:
Select Case Err
 
Case Else
MsgBox Err.Description
Resume Exit_fWorkDays
End Select
 
End Function
 
'not used in programme, reference only
Function fWorkDays1(StartDate As Date, EndDate As Date) As Long
'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
'This function calculates the number of workdays between two dates.
'The number of workdays is inclusive of the beginning and ending dates.
'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of the holiday being observed.
 
Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long
 
'Check to see if dtStartDay > dtEndDay.  If so, then switch the dates
If StartDate > EndDate Then
    dtNominalEndDay = StartDate
    StartDate = EndDate
    EndDate = dtNominalEndDay
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", StartDate, EndDate)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), StartDate)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= EndDate
    If Weekday(dtNominalEndDay) <> 1 Then
        If Weekday(dtNominalEndDay) <> 7 Then
            lngTotalDays = lngTotalDays + 1
        End If
    End If
    dtNominalEndDay = dtNominalEndDay + 1
Wend
'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("HolidayDate", "tblHolidays", "HolidayDate <= #" & EndDate & "#  AND HolidayDate >= #" & StartDate & "# AND Weekday(HolidayDate) <> 1 AND Weekday(HolidayDate) <> 7")
Debug.Print lngTotalHolidays
'Here are how many total days are between the two dates - this is inclusive of the start and end date
fWorkDays1 = lngTotalDays - lngTotalHolidays
 
End Function

[FishVal]

Hi, Kevin.

Just to ensure we are mentioning the same.
Where the error occurs, in code or in query?

[Kevin Wilcox]

Hi FishVal

Errors occur when I use the functions in queries. Here's a typical example;

SELECT *
FROM qryFilterBase
WHERE (((fworkdays([DatePosted],Date()))>10) And ((qryFilterBase.ActionID)=36) And ((qryFilterBase.DateCompleted) Is Null));

Earlier, the trigger for the problem seemed to be the use of additional criteria, i.e. if I removed the "And ((qryFilterBase.ActionID)=36) " section from the string as a test, it worked. But not today - sometimes it does, sometimes it doesn't. I don't really understand why it's failing - in this instance there's a date in the [dateposted] field for every record, and replacing that part of the string with "((Date()-[dateposted])>10)" returns just 5 or 6 records, so you'd think it wouldn't struggle too much. I suspect that this function is behind the db suddenly running incredibly slowly when split and deployed on the network - when not bothering to calculate workdays it ran quite well. Any fixes/alternative suggestions would be very helpful; I have to return around 30k - 40k records where the number of workdays is calculated, and right now I'm starting to think I should find another way altogether!

Thanks
Kevin

[FishVal]

Hi FishVal

Errors occur when I use the functions in queries. Here's a typical example;

SELECT *
FROM qryFilterBase
WHERE (((fworkdays([DatePosted],Date()))>10) And ((qryFilterBase.ActionID)=36) And ((qryFilterBase.DateCompleted) Is Null));

Earlier, the trigger for the problem seemed to be the use of additional criteria, i.e. if I removed the "And ((qryFilterBase.ActionID)=36) " section from the string as a test, it worked. But not today - sometimes it does, sometimes it doesn't. I don't really understand why it's failing - in this instance there's a date in the [dateposted] field for every record, and replacing that part of the string with "((Date()-[dateposted])>10)" returns just 5 or 6 records, so you'd think it wouldn't struggle too much. I suspect that this function is behind the db suddenly running incredibly slowly when split and deployed on the network - when not bothering to calculate workdays it ran quite well. Any fixes/alternative suggestions would be very helpful; I have to return around 30k - 40k records where the number of workdays is calculated, and right now I'm starting to think I should find another way altogether!

Thanks
Kevin

Hi, Kevin.

I've taken a look at the code. It has some weak places and doesn't seem to be optimal.

Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
On Error GoTo Err_fWorkDays
 
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
 
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
 
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
 
intCount = 0
 
Do While StartDate <= EndDate
 
If Not IsNull(StartDate) And Not IsNull(EndDate) Then
    rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
        If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
            If rst.NoMatch Then
            intCount = intCount + 1
            End If
        End If
End If
 
StartDate = StartDate + 1
 
Loop
 
fWorkDays = intCount
 
Exit_fWorkDays:
Exit Function
 
Err_fWorkDays:
Select Case Err
 
Case Else
MsgBox Err.Description
Resume Exit_fWorkDays
End Select
 
End Function
 

• Function arguments are Date type. Though you say you have no nulls in your table, it would be generally a good idea to declare them as Variants and check for Null before calculations.
• You search recordset for each day in the range. I suppose it will be more effective to check each record in recordset whether it falls in the given range.
• Saturdays and Sundays quantity may be calculated using simple math.

[Kevin Wilcox]

Hi Fishval
[*] Function arguments are Date type. Though you say you have no nulls in your table, it would be generally a good idea to declare them as Variants and check for Null before calculations.[*] You search recordset for each day in the range. I suppose it will be more effective to check each record in recordset whether it falls in the given range.[*] Saturdays and Sundays quantity may be calculated using simple math.


Thanks, I'll try these suggestions. I've already found that once I checked for nulls properly, the function worked as expected, so that's half the battle won.
Kevin