Avatar of iainmacleod
iainmacleod
Flag 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#
Else
    dtStart = Me.date1.Value
End If

If IsNull(Me.date2.Value) Then
    dtEnd = #1/1/2199#
Else
    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

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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))"
ASKER CERTIFIED SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
iainmacleod

ASKER
Rey, Unfortunately both options threw errors.

Gustav, Thanks, that worked a treat.
Gustav Brock

You are welcome!

/gustav
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes