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

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.