Simple Question on "What Date" criteria

[Guest]

I'm running a query and have the "date" field criteria set to prompt for the
date...the criteria is [What Day? Example 05/29/07]. I have thousands of
records and am keying an applicable date but nothing is being returned. If I
delete the date criteria, the query runs correctly which makes me think I'm
missing a key component of the criteria. Any help is appreciated, thanks

 

[John Spencer]

The problem could be that your parameter is not being recognized as a date
parameter.

You can fix that by declaring the parameter type.

Open the query in design view
Select Query: Parameters... from the menu
Enter [What Day? Example 05/29/07] in parameter
Select Date/Time from Data Type
Click the Ok Button

If you are feeling lazy you can also force the parameter type by using
Criteria: CDate([What Day? Example 05/29/07])


Another problem could be that the date field also contains a time component.
If that is the case
try setting the criteria as

Criteria: Between [What Day? Example 05/29/07] and DateAdd("d",1,[What
Day? Example 05/29/07] )
(that may get records on the next day that are exactly at midnight) so
safer would be

Criteria: >=[What Day? Example 05/29/07] AND <DateAdd("d",1,[What Day?
Example 05/29/07])


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Post your SQL.

The first thing I would do is create a parameter and define it as a
data/time variable. You can do this in the SQL query by typing:

PARAMETERS [What Day? Example 05/29/07] DateTime;

as the first line of your query.

Next, you need to look at how the data is actually stored in the database.
Change the format of the field to long date and determine whether there are
any times shown in the data other than 12 AM. If there is, then you will
need to use the DateValue function in the where clause of your query, like:

WHERE DateValue([yourDateField]) = [What Day? Example 05/29/07]

HTH
Dale