UK Date format in MS Access

iainmacleod
iainmacleod used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
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))"
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You don't need to "try" anything, just make sure the date values are expressed as correct strings for your filter:

Dim dtStart As Date
Dim dtEnd As Date
Dim strDateRange As String

dtStart = Nz(Me.date1.Value, #1/1/1900#)
dtEnd = Nz(Me.date2.Value, #1/1/2199#)

strDateRange = "issuedate BETWEEN #" & Format(dtStart, "yyyy\/mm\/dd") & "# AND #" & Format(dtEnd, "yyyy\/mm\/dd") & "#"
Me.Filter = strDateRange
Me.FilterOn = True

Open in new window

/gustav

Author

Commented:
Rey, Unfortunately both options threw errors.

Gustav, Thanks, that worked a treat.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial