General discussion

Locked

date comparison in Access

By john_wills ·
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.

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

date comparison in Access

by Seagull Data Services In reply to date comparison in Access

How are you doing this?
Filters? Queries? in code?
And have you looked at the resulting sql string if it's a query/code?

Collapse -

date comparison in Access

by john_wills In reply to date comparison in Access

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.

Collapse -

date comparison in Access

by john_wills In reply to date comparison in Access

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.

Collapse -

date comparison in Access

by sl-campbell In reply to date comparison in Access

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.

Collapse -

date comparison in Access

by sl-campbell In reply to date comparison in Access

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

Collapse -

date comparison in Access

by john_wills In reply to date comparison in Access

Poster rated this answer

Collapse -

date comparison in Access

by john_wills In reply to date comparison in Access

I have checked the criteria string with MsgBox and it looks fine.

Collapse -

date comparison in Access

by Seagull Data Services In reply to date comparison in Access

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.

Collapse -

date comparison in Access

by john_wills In reply to date comparison in Access

Poster rated this answer

Collapse -

date comparison in Access

by john_wills In reply to date comparison in Access

This question was closed by the author

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums