Discussion Locked

date comparison in Access

0 helpful
10 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I append " and [SessionDate]>=datevalue('" & Me![StartingDate] & "')" to a criterion that works, then a similar for the end date, and expect a restricted view of the continuous form I open. Instead I get everything the already working criterion lets through, as though the date criteria had not worked.

10 replies

by john_wills

It turns out that the problem was not with the date comparison but with filter application. Because the invoked form has DataEntry (un)setting, the filter was turned off. Sorry for the misleading question.

by john_wills

john_fl asks how I am doing it. I construct criteria for OpenForm within a VBA procedure invoked by clicking a button. The two dates are controls on the same form as the button.

by sl-campbell

It would help to see then complete criteria string, or better yet, put a debug.print on the criteria part, or put a toggle/stopper on the OpenForm and copy the sql STRING portion to the immediate test window as in:
?" and [SessionDate]>=datevalue('" & Me![StartingDate] & "')" and press ENTER.
Then you can inspect the actual values.
Or build a query in the Query builder and use the same criteria except relace the
" & Me![StartingDate] & "' with actual dates in order to test it.

Maybe the Me![StartingDate] doesn't contain an actual date string at the time the query is ran.
Or the field [SessionDate] is a string field in which case you will need to change it to a date field or change the criteria syntax to:

" and DATEVALUE([SessionDate])>=datevalue('" & Me![StartingDate] & "')" or

" and [SessionDate]>=#" & Me![StartingDate] & "#" (if the date is in the correct U.S. format)

Also, you may want to consider using BETWEEN myStartDate AND myEndDate.

by sl-campbell

Please show a copy of the complete criteria at run time (with actual values).
Only this way can correct help be given.

by seagull data services

Have you tried surrounding both your date criteria with parentheses()?
It occurs to me that you're adding this to existing criteria, e.g.
"select * from mytable where mycityfield = 'cannes' and mydate >= startingdate and mydate <= finishdate;"
This could be misinterpreted as any two conditions together.
However
"select * from mytable where mycityfield = 'cannes' and (mydate >= startingdate and mydate <= finishdate);"
specifies that the date criteria should be considered together.

This discussion is closed to new replies.