Problems with MS Access reading dates as US, not British formats.


Will Flanagan

Hi Folks,

I downloaded a database off the MSDN website (web
address: It
calculates the number of working days and takes off any holidays which are
stored in a separate table. It works fine, but the only problem is that
when I enter dates in the holiday table, it reads it as the US format of
m/d/yy instead of UK d/m/yy. I have set the regional settings to UK
time/date so my dates in the Holidays table are displayed as UK, but I have
to remember to enter the dates in US order, for the system to work. (I know
this will cause confusion for users later on).

The problematic code is:

Public Function Workdays(ByRef startDate As Date, _
ByRef endDate As Date, _
Optional ByRef strHolidays As String = "Holidays" _
) As Integer
' Returns the number of workdays between startDate
' and endDate inclusive. Workdays excludes weekends and
' holidays. Optionally, pass this function the name of a table
' or query as the third argument. If you don't the default
' is "Holidays".
On Error GoTo Workdays_Error
Dim nWeekdays As Integer
Dim nHolidays As Integer
Dim strWhere As String

' DateValue returns the date part only.
startDate = DateValue(startDate)
endDate = DateValue(endDate)

nWeekdays = Weekdays(startDate, endDate)
If nWeekdays = -1 Then
Workdays = -1
GoTo Workdays_Exit
End If

strWhere = "[Holiday] >= #" & startDate _
& "# AND [Holiday] <= #" & endDate & "#"

' Count the number of holidays.
nHolidays = DCount(Expr:="[Holiday]", _
Domain:=strHolidays, _

Workdays = nWeekdays - nHolidays

Exit Function

Workdays = -1
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Workdays"
Resume Workdays_Exit

End Function

The dates are stored in the [Holiday] field of the Holidays table.

I am sure that there is something really simple I need to do in this Visual
Basic code to make sure it reads all dates as UK, but I am completely stuck!!

I didn't know if anyone would have any ideas?

Many thanks,


Alex Dybenko

first - check that you get correct dates into function, then you have to
change this line:

strWhere = "[Holiday] >= #" & format(startDate,"mm\/dd\/yyyy") _
& "# AND [Holiday] <= #" & Format(endDate,"mm\/dd\/yyyy") & "#"

Jet correctly understand dates, passed in mm/dd/yyyy format

Best regards,
Alex Dybenko (MVP)


Acces will always refer to US date format when using date variable in code.
You can SEE Uk format but you should USE US format in code or SQL statements
(all the use the US format for date, is an international convention on any
RDBMS and more general in programming).
Use the format property for the table field to show the date in your desired
format but use the format function in code to have date in US format:
Format(myDate, "\#mm\/dd\/yyyy\#")


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question