Date/Time in a query

[Guest]

I have an Acess DB with linked SQL tables using ODBC. I am trying to run a
query which uses a form to specify criteria. Here's the criteria in the Date
field:

Between [Forms]![frmChooseReqDate]![StartDate] And
[Forms]![frmChooseReqDate]![EndDate]

The Date field is date/time - and will only return data if I type in exact
times. I want to be able to enter just a date and get all times for that
date. I'm sure it can be done using wildcards, but I don't know the correct
syntax for the above criteria.

Any help is appreciated!

 

[Jeff Boyce]

Take a look at the DateValue() function (if I recall correctly).

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[fredg]

I have an Acess DB with linked SQL tables using ODBC. I am trying to run a
query which uses a form to specify criteria. Here's the criteria in the Date
field:

Between [Forms]![frmChooseReqDate]![StartDate] And
[Forms]![frmChooseReqDate]![EndDate]

The Date field is date/time - and will only return data if I type in exact
times. I want to be able to enter just a date and get all times for that
date. I'm sure it can be done using wildcards, but I don't know the correct
syntax for the above criteria.

Any help is appreciated!

Your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.

Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary elsewhere, is
to run an Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new record entries by
use Date() instead of Now() as the value entered.