Working with dates in a query

[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

 

[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.

 

[Squid]

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

Thank you very much!

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!)

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

 

[Van T. Dinh]

If you meant you used something like:

.... WHERE DateValue([DateField]) = [RequiredDate]

then Doug's solution is _much_ better. With something like the above
criteria, the DateValue function will have to be evaluated for each Record
in your Table before comparing to the [RequiredDate]. If your Table only
has a small number of Records, this is OK but for large number of Records,
repeated evaluation of DateValue will slow your query down.

In addition, if you index the [DateField], Doug's method will utilize the
index to retrieve Record quickly. If you use the DateValue() function, JET
won't be able to use the index.

 

[Squid]

In the data entry field, I have the default value property set at
=DateValue(Now())

If you meant you used something like:

... WHERE DateValue([DateField]) = [RequiredDate]

then Doug's solution is _much_ better. With something like the above
criteria, the DateValue function will have to be evaluated for each Record
in your Table before comparing to the [RequiredDate]. If your Table only
has a small number of Records, this is OK but for large number of Records,
repeated evaluation of DateValue will slow your query down.

In addition, if you index the [DateField], Doug's method will utilize the
index to retrieve Record quickly. If you use the DateValue() function,
JET
won't be able to use the index.

--
HTH
Van T. Dinh
MVP (Access)

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

Thank you very much!

 

[Douglas J. Steele]

Why not set the default value to Date() instead?

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

In the data entry field, I have the default value property set at
=DateValue(Now())

If you meant you used something like:

... WHERE DateValue([DateField]) = [RequiredDate]

then Doug's solution is _much_ better. With something like the above
criteria, the DateValue function will have to be evaluated for each Record
in your Table before comparing to the [RequiredDate]. If your Table only
has a small number of Records, this is OK but for large number of Records,
repeated evaluation of DateValue will slow your query down.

In addition, if you index the [DateField], Doug's method will utilize the
index to retrieve Record quickly. If you use the DateValue() function,
JET
won't be able to use the index.

--
HTH
Van T. Dinh
MVP (Access)

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

Thank you very much!