Solved Code cannot differentiate between regional dates

[Ajabu]

I'm trying to count the number of workdays in a month, excluding weekends and holidays as listed in a separate table.

In order to do this I'm using the function developed by Arvin Meyer:

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:     WorkingDays2
' Inputs:     StartDate As Date
'     EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:     May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

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

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

Simple...as long as the computer's regional settings are set to US.

The problem is my Access dates are in British format (dd/mm/yyyy) but the DAO Recordset retrieves the holiday dates in the US format (mm/dd/yyyy).

How do I resolve this (without changing the regional settings on the computer to conform to US settings)?

Any help would be appreciated. Thanks.

 

[arnelgp]

you need to change this portion:

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"


TO:

rst.FindFirst "[HolidayDate] = #" & Format$(StartDate, "mm/dd/yyyy") & "#"

 

[Ajabu]

How long I looked for a solution, believing the answer was so simple. Turns out I was right. But I was defeated nonetheless.

Thank you so much!

 

[arnelgp]

did it solved your Regional Setting issue?

 

[Gasman]

How long I looked for a solution, believing the answer was so simple. Turns out I was right. But I was defeated nonetheless.

Thank you so much!

Remember this for the future, or make all dates yyyy-mm-dd when formatting

 

[Ajabu]

Fixed it? Yes it did. I realise I forgot to add that important point on my last message though I did mark the question as solved.

I can't tell you how many different things I tried, some quite close to what you suggested, all without success. But your solution worked. Thank you!