Problem with date criteria in Query

[rb608]

I have a form (RenewalReports) that I use to print a report (using a
cmd button) based on various user inputs on the form. One of the user
inputs is the data range. On the form, I have two text box controls,
txtDateStart and txtDateEnd.

The data source for the report is a query that draws data from several
tables based on the form-based criteria. All is working fine except
the date range, which the query seems intent on ignoring.

The report is intended to produce a listing of companies based on the
renewal dates of a safety class. Renewal dates are based on either
one or two years, depending on the class, so the query has a field
RenewalDateateAdded("yyyy",[RenewalPeriod],Date())
This works fine and produces the correct renewal dates for the
respective classes.

To allow the user to look ahead and see which companies have renewals
approaching, I want to filter the query results within a specified
date range as mentioned above, so on the criteria line for the
RenewalDate field, I have the criteria "Between [Forms]!
[RenewalReports]![txtDateStart] and [Forms]![RenewalReports]!
[txtDateEnd].

If I run the query as a standalone (without the referenced form open),
it prompts me for the start and end dates as parameters, and if I
manually enter a set of dates, e.g. 6/01/09 and 9/01/09, it returns no
records.

If I run the query from the form, with the same dates in the text
boxes, it returns all records without applying the criteria at all.

But wait, there's more. I also entered default values for the dates
on the form, and if I run the query without chaging anything, it works
perfectly; but if I change one date, the criteria is ignored.

FWIW, all relevent controls and fields are formatted as dates.

I also tried using the report filter instead of the query criteria and
got pretty much the same results.

Surely I'm entering the text box addresses in the wrong format or
syntax, but I'm temporarily stumped.

I *know* this has to be something really simple; but my brain is
tired.

TIA,
Joe

 

[Jerry Whittle]

Do you have the parameters set as Date/Time in the query? In design view
Query, Parameters (at least up to A03).

Are you using the American M/D/YY format or something like D/M/YY? Try the
American standard. However your example seems to be looking ahead and makes
me think that you are using M/D/YY.

Maybe it's the YY. Try 4-digit years.

Maybe it's the dates. 6/01/09 = 0.6666667

Try something like 1 June 2008

 

[rb608]

Do you have the parameters set as Date/Time in the query? In design view
Query, Parameters (at least up to A03).

Are you using the American M/D/YY format or something like D/M/YY?  Trythe
American standard. However your example seems to be looking ahead and makes
me think that you are using M/D/YY.

Thanks for the suggestions. You got me to tinkering with the format,
and I happened upon the answer. Turns out that the query was
sensitive to whether the date was enetered as 6/1/2008 (worked) or
6/01/2008 (didn't work). Odd I've never run into that before. I
wouldn't have even considered it possible without your input.

That led me to looking at the form itself to make sure the user's
input was appropriately formatted, and I found that indeed one of the
text boxes was not formatted correctly.

All seems to be well now. Thanks again.

Joe F.