Date Function anomaly

[OzNet]

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:

HolidayKey Holiday Holiday Date WorkDay
1 New Year's Day 1/01/2009 No
2 Australia Day 26/01/2009 No
3 Good Friday 10/04/2009 No
4 Easter Saturday 11/04/2009 No
5 Easter Monday 13/04/2009 No
6 Anzac Day 27/04/2009 No
7 Queen's Birthday 8/06/2009 No
8 Christmas Day 25/12/2009 No
9 Boxing Day 28/12/2009 No
10 Labor Day 4/05/2009 No
11 Ekka Show Day 3/08/2009 No

The function to analyse the table:

Function PublicHolidayCount(dtmBegin As Date, dtmEnd As Date) As Long
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim rv As Long
Set db = CurrentDb
strSQL = "SELECT tblPublicHolidays.HolidayDate, tblPublicHolidays.WorkDay, " & _
"Format([HolidayDate],'ddd') AS Day " & _
" FROM tblPublicHolidays " & _
"WHERE (((tblPublicHolidays.HolidayDate) >= #" & dtmBegin & "# " & _
"And (tblPublicHolidays.HolidayDate) <= #" & dtmEnd & "# ) " & _
"AND ((tblPublicHolidays.WorkDay)=False) " & _
" AND ((Format([HolidayDate],'ddd'))<>'Sat' And (Format([HolidayDate],'ddd'))<>'Sun'));"
 
Set rst = db.OpenRecordset(strSQL)
If rst.EOF And rst.BOF Then
rv = 0
Else
rst.MoveLast
rv = rst.RecordCount
End If
 
PublicHolidayCount = rv
End Function

The Intermediate window test

?PublicHolidayCount(#1/2/2009#,#28/2/2009#) returns 0 which is correct (Australian date format)
?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

Private Sub cmdDates_Click()
Dim intTotalWeekdays As Integer
Dim intHolidaysOff As Integer
Dim TotalWorkDays As Integer
Dim dtmBegin As Date
Dim dtmEnd As Date
dtmBegin = Me.txtDateBegin / retrieves dates from form
dtmEnd = Me.txtDateEnd
 
MsgBox "Start Date: " & dtmBegin /only here for testing purposes – shows dates in Australian format.
MsgBox "End Date: " & dtmEnd
 
intTotalWeekdays = TotalWeekdays(dtmBegin, dtmEnd) / passes dates to another function to calculate week days.
 
MsgBox "Total Weekdays: " & intTotalWeekdays / only here for testing purposes – results are accurate
 
 
intHolidaysOff = PublicHolidayCount(dtmBegin, dtmEnd) / THIS IS WHERE THE PROBLEM IS – it should be passing the dates in Australian format but is returning incorrect results
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.
 
MsgBox "Public Holidays: " & intHolidaysOff / Only here for testing purpose
TotalWorkDays = intTotalWeekdays - intHolidaysOff
 
MsgBox "Total Work Days: " & TotalWorkDays / Only here for testing purposes
 
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?

[missinglinq]

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,

12/31/2008

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

6/1/2008

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)>

[NeoPa]

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 :

SELECT #1/2/2009# AS Jan2

See Literal DateTimes and Their Delimiters (#) for a more in-depth discussion.

[OzNet]

Thank you Linq ;0)> and NeoPa for your responses. I appreciate it.

Cheers

[NeoPa]

You're welcome :)