469,643 Members | 2,036 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,643 developers. It's quick & easy.

Date Function anomaly

I have some functions to calculate the working days in a given period. This includes a table that is queried to calculate the number of public holidays that don’t occur on a weekend.

If I test the function using the intermediate window, it works fine. However, when I pass the dates from the code attached to my form, the results are inaccurate.

You will notice my dates are in Australian format. Everything works fine using the Australian date format except the passing of the dates from a variable to the PublicHolidayCount function. I have found I need to reformat the dates in my form code to US date format to achieve an accurate result.

Can someone tell me why?

Here is my table:
Expand|Select|Wrap|Line Numbers
  1. HolidayKey Holiday Holiday Date WorkDay
  2. 1 New Year's Day 1/01/2009 No
  3. 2 Australia Day 26/01/2009 No
  4. 3 Good Friday 10/04/2009 No
  5. 4 Easter Saturday 11/04/2009 No
  6. 5 Easter Monday 13/04/2009 No
  7. 6 Anzac Day 27/04/2009 No
  8. 7 Queen's Birthday 8/06/2009 No
  9. 8 Christmas Day 25/12/2009 No
  10. 9 Boxing Day 28/12/2009 No
  11. 10 Labor Day 4/05/2009 No
  12. 11 Ekka Show Day 3/08/2009 No
The function to analyse the table:
Expand|Select|Wrap|Line Numbers
  1. Function PublicHolidayCount(dtmBegin As Date, dtmEnd As Date) As Long
  2. Dim db As DAO.Database
  3. Dim rst As DAO.Recordset
  4. Dim strSQL As String
  5. Dim rv As Long
  6. Set db = CurrentDb
  7. strSQL = "SELECT tblPublicHolidays.HolidayDate, tblPublicHolidays.WorkDay, " & _
  8. "Format([HolidayDate],'ddd') AS Day " & _
  9. " FROM tblPublicHolidays " & _
  10. "WHERE (((tblPublicHolidays.HolidayDate) >= #" & dtmBegin & "# " & _
  11. "And (tblPublicHolidays.HolidayDate) <= #" & dtmEnd & "# ) " & _
  12. "AND ((tblPublicHolidays.WorkDay)=False) " & _
  13. " AND ((Format([HolidayDate],'ddd'))<>'Sat' And (Format([HolidayDate],'ddd'))<>'Sun'));"
  15. Set rst = db.OpenRecordset(strSQL)
  16. If rst.EOF And rst.BOF Then
  17. rv = 0
  18. Else
  19. rst.MoveLast
  20. rv = rst.RecordCount
  21. End If
  23. PublicHolidayCount = rv
  24. End Function
The Intermediate window test
Expand|Select|Wrap|Line Numbers
  1. ?PublicHolidayCount(#1/2/2009#,#28/2/2009#) returns 0 which is correct (Australian date format)
  2. ?PublicHolidayCount(#2/1/2009#,#2/28/2009#) returns 1 which is incorrect (US date format)
The code on my form with some extra notes of explanation
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDates_Click()
  2. Dim intTotalWeekdays As Integer
  3. Dim intHolidaysOff As Integer
  4. Dim TotalWorkDays As Integer
  5. Dim dtmBegin As Date
  6. Dim dtmEnd As Date
  7. dtmBegin = Me.txtDateBegin / retrieves dates from form
  8. dtmEnd = Me.txtDateEnd
  10. MsgBox "Start Date: " & dtmBegin /only here for testing purposes – shows dates in Australian format.
  11. MsgBox "End Date: " & dtmEnd
  13. intTotalWeekdays = TotalWeekdays(dtmBegin, dtmEnd) / passes dates to another function to calculate week days.
  15. MsgBox "Total Weekdays: " & intTotalWeekdays / only here for testing purposes – results are accurate
  18. intHolidaysOff = PublicHolidayCount(dtmBegin, dtmEnd) / THIS IS WHERE THE PROBLEM IS – it should be passing the dates in Australian format but is returning incorrect results
  19. intHolidaysOff = PublicHolidayCount(Format(dtmBegin, "m/d/yy"), Format(dtmEnd, "m/d/yy")) / THIS CORRECTS THE PROBLEM – converts the date format to US format and the results are accurate.
  21. MsgBox "Public Holidays: " & intHolidaysOff / Only here for testing purpose
  22. TotalWorkDays = intTotalWeekdays - intHolidaysOff
  24. MsgBox "Total Work Days: " & TotalWorkDays / Only here for testing purposes
  26. End Sub
The Question

So, why does the PublicHolidayCount function work accurately with Australian dates passed from the Intermediate window but not when passed from the code attached to my form which requires the dates to be reformatted into US date format?
Jan 23 '09 #1
4 2783
3,532 Expert 2GB
Using dates in any format other than US format generally leads to problems in Access. A fellow Aussie, Allen Browne, documents this here and gives some workarounds:

Microsoft Access tips: International Dates in Access

As to why it works from the Immediate Window but not from the module, I'm not sure. Access does process things somewhat differently from Immediate Window; this sort of thing pops up, from time to time. The other possibility is that the dates being processed from the Immediate Window are unequivocal dates whereas those from the module are not.

The problems caused by international date formats usually only apply to dates where there could be more than one interpretation. For example,


can only be interpreted as December 31, 2008; the 31 can only represent a day, not a month. But


could be June 1, 2008 or January 6, 2008. Both the 6 and the 1 could represent days or months, and hence the problem.

Linq ;0)>
Jan 23 '09 #2
32,203 Expert Mod 16PB
Fundamentally, because dates in SQL are ALWAYS interpreted in SQL standard form, which just happens to be US format.

Access does a good job of hiding this from users as it will convert dates from local format to SQL format for you whenever IT is doing it (IE. Not when looking at the SQL directly but pretty well all other times).

To see this clearly create a simple query and add a single field (called Jan2) as "Jan2: #2 Jan#". You will see this in the grid, in your own short date format. Next use the View menu to view the SQL. You will now see this as :
Expand|Select|Wrap|Line Numbers
  1. SELECT #1/2/2009# AS Jan2
See Literal DateTimes and Their Delimiters (#) for a more in-depth discussion.
Jan 23 '09 #3
Thank you Linq ;0)> and NeoPa for your responses. I appreciate it.

Jan 23 '09 #4
32,203 Expert Mod 16PB
You're welcome :)
Jan 23 '09 #5

Post your reply

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

Similar topics

4 posts views Thread by Hans Gruber | last post: by
17 posts views Thread by clintonG | last post: by
9 posts views Thread by insomniux | last post: by
1 post views Thread by mai | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.