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
If IsNull(Me.date2.Value) Then
dtEnd = #1/1/2199#
dtEnd = Me.date2.Value
strDateRange = "(issuedate BETWEEN #" & dtStart & "# AND #" & dtEnd & "#)"
Me.Filter = strDateRange
Me.FilterOn = True