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?
-
-
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. -
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.
Code: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 FunctionComment
-
Hi, Kevin.
Just to ensure we are mentioning the same.
Where the error occurs, in code or in query?Comment
-
Hi FishVal
Errors occur when I use the functions in queries. Here's a typical example;
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!Code:SELECT * FROM qryFilterBase WHERE (((fworkdays([DatePosted],Date()))>10) And ((qryFilterBase.ActionID)=36) And ((qryFilterBase.DateCompleted) Is Null));
Thanks
KevinComment
-
Hi, Kevin.Originally posted by Kevin Wilcox
I've taken a look at the code. It has some weak places and doesn't seem to be optimal.
[code=vb]
Public Function fWorkDays(Start Date 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.OpenRecordse t("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(StartDat e) And Not IsNull(EndDate) Then
rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDa te) <> vbSunday And Weekday(StartDa te) <> 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
[/code]
- 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.
Comment
-
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.
KevinComment
Comment