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

iainmacleodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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))"
0
Gustav BrockCIOCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
iainmacleodAuthor Commented:
Rey, Unfortunately both options threw errors.

Gustav, Thanks, that worked a treat.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.