By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,471 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

workdays function issue

P: 68
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?
Oct 17 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Oct 17 '07 #2

P: 68
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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. 'not used in programme, causes problems when using with criteria in filtered events "all other breaches"
  4. '... but sql equivalent does work.
  5. Public Function fWeekends(StartDate As Date) As Integer
  6.  
  7. Select Case Weekday(StartDate)
  8. Case 5
  9. fWeekends = (Date - StartDate) - 3
  10. Case 6
  11. fWeekends = (Date - StartDate) - 2
  12. Case Else
  13. fWeekends = (Date - StartDate) - 1
  14. End Select
  15.  
  16. End Function
  17. Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
  18. On Error GoTo Err_fWorkDays
  19.  
  20. Dim intCount As Integer
  21. Dim rst As DAO.Recordset
  22. Dim DB As DAO.Database
  23.  
  24. Set DB = CurrentDb
  25. Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
  26.  
  27. 'StartDate = StartDate + 1
  28. 'To count StartDate as the 1st day comment out the line above
  29.  
  30. intCount = 0
  31.  
  32. Do While StartDate <= EndDate
  33.  
  34. If Not IsNull(StartDate) And Not IsNull(EndDate) Then
  35.     rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
  36.         If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
  37.             If rst.NoMatch Then
  38.             intCount = intCount + 1
  39.             End If
  40.         End If
  41. End If
  42.  
  43. StartDate = StartDate + 1
  44.  
  45. Loop
  46.  
  47. fWorkDays = intCount
  48.  
  49. Exit_fWorkDays:
  50. Exit Function
  51.  
  52. Err_fWorkDays:
  53. Select Case Err
  54.  
  55. Case Else
  56. MsgBox Err.Description
  57. Resume Exit_fWorkDays
  58. End Select
  59.  
  60. End Function
  61.  
  62. 'not used in programme, reference only
  63. Function fWorkDays1(StartDate As Date, EndDate As Date) As Long
  64. 'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
  65. 'This function calculates the number of workdays between two dates.
  66. 'The number of workdays is inclusive of the beginning and ending dates.
  67. 'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
  68. 'The table must list the dates of holidays to be observed.
  69. 'The user may include other fields in that table, for example, a description of the holiday being observed.
  70.  
  71. Dim lngTotalDays As Long
  72. Dim lngTotalWeeks As Long
  73. Dim dtNominalEndDay As Date
  74. Dim lngTotalHolidays As Long
  75.  
  76. 'Check to see if dtStartDay > dtEndDay.  If so, then switch the dates
  77. If StartDate > EndDate Then
  78.     dtNominalEndDay = StartDate
  79.     StartDate = EndDate
  80.     EndDate = dtNominalEndDay
  81. End If
  82. 'Here are how many weeks are between the two dates
  83. lngTotalWeeks = DateDiff("w", StartDate, EndDate)
  84. 'Here are the number of weekdays in that total week
  85. lngTotalDays = lngTotalWeeks * 5
  86. 'Here is the date that is at the end of that many weeks
  87. dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), StartDate)
  88. 'Now add the number of weekdays between the nominal end day and the actual end day
  89. While dtNominalEndDay <= EndDate
  90.     If Weekday(dtNominalEndDay) <> 1 Then
  91.         If Weekday(dtNominalEndDay) <> 7 Then
  92.             lngTotalDays = lngTotalDays + 1
  93.         End If
  94.     End If
  95.     dtNominalEndDay = dtNominalEndDay + 1
  96. Wend
  97. 'Here are how many holiday days there are between the two days
  98. lngTotalHolidays = DCount("HolidayDate", "tblHolidays", "HolidayDate <= #" & EndDate & "#  AND HolidayDate >= #" & StartDate & "# AND Weekday(HolidayDate) <> 1 AND Weekday(HolidayDate) <> 7")
  99. Debug.Print lngTotalHolidays
  100. 'Here are how many total days are between the two dates - this is inclusive of the start and end date
  101. fWorkDays1 = lngTotalDays - lngTotalHolidays
  102.  
  103. End Function
Oct 24 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Hi, Kevin.

Just to ensure we are mentioning the same.
Where the error occurs, in code or in query?
Oct 24 '07 #4

P: 68
Hi FishVal

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

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM qryFilterBase
  3. 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
Oct 25 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Hi FishVal

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

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM qryFilterBase
  3. 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.

Expand|Select|Wrap|Line Numbers
  1. Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
  2. On Error GoTo Err_fWorkDays
  3.  
  4. Dim intCount As Integer
  5. Dim rst As DAO.Recordset
  6. Dim DB As DAO.Database
  7.  
  8. Set DB = CurrentDb
  9. Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
  10.  
  11. 'StartDate = StartDate + 1
  12. 'To count StartDate as the 1st day comment out the line above
  13.  
  14. intCount = 0
  15.  
  16. Do While StartDate <= EndDate
  17.  
  18. If Not IsNull(StartDate) And Not IsNull(EndDate) Then
  19.     rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
  20.         If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
  21.             If rst.NoMatch Then
  22.             intCount = intCount + 1
  23.             End If
  24.         End If
  25. End If
  26.  
  27. StartDate = StartDate + 1
  28.  
  29. Loop
  30.  
  31. fWorkDays = intCount
  32.  
  33. Exit_fWorkDays:
  34. Exit Function
  35.  
  36. Err_fWorkDays:
  37. Select Case Err
  38.  
  39. Case Else
  40. MsgBox Err.Description
  41. Resume Exit_fWorkDays
  42. End Select
  43.  
  44. End Function
  45.  
  • 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.
Oct 25 '07 #6

P: 68
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
Oct 25 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.