Avatar of iainmacleod
iainmacleodFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

UK Date format in MS Access

My users have just brought to my attention an issue with running some filters and searches in MS Access.
Results returned are wrong due to date formats.
Dates are stored correctly in tables and entered on forms in UK format however results come as US date.
I believe that this post I read elsewhere is relevant:
"Access will read dates delimited by the # signs as in the US date format if both the day and month are less than 13 e.g. #07/02/2004# is July 2nd, 2004. On machines with UK dates, you can use the DateValue() function instead of the # signs."

My question is how I can use datevalue() or solve this problem when I have code such as the Filter code attached and also within macros where I open a form with conditions.
Dim dtStart As Date
Dim dtEnd As Date
Dim strDateRange As String

If IsNull(Me.date1.Value) Then
    dtStart = #1/1/1900#
    dtStart = Me.date1.Value
End If

If IsNull(Me.date2.Value) Then
    dtEnd = #1/1/2199#
    dtEnd = Me.date2.Value
End If

strDateRange = "(issuedate BETWEEN #" & dtStart & "# AND #" & dtEnd & "#)"
Me.Filter = strDateRange

Me.FilterOn = True

Open in new window

Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try this

strDateRange = "(dateValue(issuedate) BETWEEN " & datevalue(dtStart) & " AND " & datevalue(dtEnd) & ")"
debug.print strDateRange  ' this will print the formatted string, see if the dates are all in the same format

you can also try using

strDateRange="dateserial(year([issuedate]), month([issuedate]),day([issuedate])) between dateserial(year(dtStart),month(dtStart),day(dtStart)) and dateserial(year(dtEnd),month(dtEnd),day(dtEnd))"
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of iainmacleod
Flag of United Kingdom of Great Britain and Northern Ireland image


Rey, Unfortunately both options threw errors.

Gustav, Thanks, that worked a treat.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

You are welcome!

Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo