Reading a record set, code reads date as American not English

[Hutton]

This code works fine, the Windows regional settings are UK
(dd/mm/yyyy) but when it reads the record set it recognises them as US
dates - mm/dd/yyyy. All dates are formatted as short dates. Would like
to know how to overcome this and why it does it.
Public Function HowDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)
MyDate = FormatDateTime(StartDate, vbShortDate)
For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] =" & NumSgn & MyDate &
NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1

Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

HowDays = NumDays

End Function

[Allen Browne]

Access uses one format in the interface (so it shows as the user expects),
and a different format for VBA code and SQL statements (so a program works
the same way where ever it is run).

To avoid the 3 cases where Access can misunderstand your dd/mm/yyyy dates,
see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Hutton" <no***********@hotmail.com> wrote in message
news:7a**************************@posting.google.c om...

This code works fine, the Windows regional settings are UK
(dd/mm/yyyy) but when it reads the record set it recognises them as US
dates - mm/dd/yyyy. All dates are formatted as short dates. Would like
to know how to overcome this and why it does it.
Public Function HowDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)
MyDate = FormatDateTime(StartDate, vbShortDate)
For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] =" & NumSgn & MyDate &
NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1

Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

HowDays = NumDays

End Function

[Hutton]

Thanks Allen that has solved it

no***********@hotmail.com (Hutton) wrote in message news:<7a**************************@posting.google. com>...

This code works fine, the Windows regional settings are UK
(dd/mm/yyyy) but when it reads the record set it recognises them as US
dates - mm/dd/yyyy. All dates are formatted as short dates. Would like
to know how to overcome this and why it does it.
Public Function HowDays(StartDate As Date, EndDate As Date) As Integer

'Get the number of workdays between the given dates

Dim dbs As DAO.Database
Dim rstHolidays As DAO.Recordset

Dim Idx As Long
Dim MyDate As Date
Dim NumDays As Long
Dim strCriteria As String
Dim NumSgn As String * 1

Set dbs = CurrentDb
Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

NumSgn = Chr(35)
MyDate = FormatDateTime(StartDate, vbShortDate)
For Idx = CLng(StartDate) To CLng(EndDate)
Select Case (Weekday(MyDate))
Case Is = 1 'Sunday
'Do Nothing, it is NOT a Workday

Case Is = 7 'Saturday
'Do Nothing, it is NOT a Workday

Case Else 'Normal Workday
strCriteria = "[HoliDate] =" & NumSgn & MyDate &
NumSgn
rstHolidays.FindFirst strCriteria
If (rstHolidays.NoMatch) Then
NumDays = NumDays + 1

Else
'Do Nothing, it is NOT a Workday
End If

End Select

MyDate = DateAdd("d", 1, MyDate)

Next Idx

HowDays = NumDays

End Function