Working with dates in a query

S

Squid

I have a form which populates a table. The date field on the form is
formatted as short date with a default value of =Now(), and this same format
is used in the table. When I use a parameter query for this date field, the
query doesn't work. But if I would manually enter the date in the field,
the query would work. Common sense tells me its the =now() formula
returning the mm/dd/yyyy hh:mm:ss, instead of just the date. Is there
anyway to use =now() or another control such as date picker and have the
query work?

TIA
Mike
 
D

Douglas J. Steele

You're not really giving enough details, but I'll guess that you entered a
parameter of 2004-12-01, hoping to get all of the records entered today, and
didn't get anything. If that's the case, it's because you've stored times
along with the date, and so nothing's matching.

You have several options. You can add a computed field to your query that
only contains the date, and put your parameter against that (use the
DateValue function to return just the date). Another option (actually more
efficient) is to change your WHERE clause from WHERE MyDateField = [Input
Date] to WHERE MyDateField BETWEEN [Input Date] AND DateAdd("d", 1, [Input
Date])

If I've misinterpretted your problem, post back with more details.
 
S

Squid

Nope, you are fluent in gibberish (unintended). The datevalue function
solved my problem.

Thank you very much!


Douglas J. Steele said:
You're not really giving enough details, but I'll guess that you entered a
parameter of 2004-12-01, hoping to get all of the records entered today,
and
didn't get anything. If that's the case, it's because you've stored times
along with the date, and so nothing's matching.

You have several options. You can add a computed field to your query that
only contains the date, and put your parameter against that (use the
DateValue function to return just the date). Another option (actually more
efficient) is to change your WHERE clause from WHERE MyDateField = [Input
Date] to WHERE MyDateField BETWEEN [Input Date] AND DateAdd("d", 1, [Input
Date])

If I've misinterpretted your problem, post back with more details.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Squid said:
I have a form which populates a table. The date field on the form is
formatted as short date with a default value of =Now(), and this same format
is used in the table. When I use a parameter query for this date field, the
query doesn't work. But if I would manually enter the date in the field,
the query would work. Common sense tells me its the =now() formula
returning the mm/dd/yyyy hh:mm:ss, instead of just the date. Is there
anyway to use =now() or another control such as date picker and have the
query work?

TIA
Mike
Click to expand...
Click to expand...
 
Top