Date Criteria

[RMires]

I'm trying to run a query using a date from a form as criteria, but the query
responds as if there is no filter criteria. Is there some particular format I
have to use when using dates as criteria?

 

[kc-mass]

Why don't you upload the sql for your query.

With the query open in design view in the query design panel, right click on
the top bar
and select SQL; copy the text there and paste it into your message.

Regards

Kevin

 

[Bob Barrows]

I'm trying to run a query using a date from a form as criteria, but
the query responds as if there is no filter criteria. Is there some
particular format I have to use when using dates as criteria?

You likely need to add the form control to the Parameters dialog (Query |
Parameters), specifying the data type.

Also, as to format, Jet requires literal date values to be formatted in US
format (mm/dd/yyyy) or ISO format (yyyy-mm-dd - recommended)

 

[Rick Brandt]

I'm trying to run a query using a date from a form as criteria, but the
query responds as if there is no filter criteria. Is there some particular
format I have to use when using dates as criteria?

Post the SQL of your Query. It should look something like...

PARAMETERS Forms!FormName!DateControl DateTime;
SELECT * FROM SomeTable
WHERE SomeTable.DateField = Forms!FormName!DateControl;

As long as what is in the control on the form is in the format of a date
then the query will ordinarily assume it is a date. Using the parameter
entry (the first line above) forces the issue and is more reliable.

 

[John W. Vinson]

I'm trying to run a query using a date from a form as criteria, but the query
responds as if there is no filter criteria. Is there some particular format I
have to use when using dates as criteria?

It can often help to use the query's Parameters collection to specify the
datatype of the criterion. Right mouseclick the grey background of the tables
in query design, and select Parameters from the dropdown.

In the grid that comes up copy and paste the actual criterion (it must match
exactly), e.g. [Forms]![MyForm]![txtDate], into the left column, and choose
"Date/time" in the right column. Do this for each parameter if you have more
than one.

That said, a date criterion must be recognizable as a real date - 1/9 will
work (January 9 of the current year), 2/11/2008 will work, but 01092010 or
1995 or "Next week" will not.