diagnosing query that references user imputed dates from form

[quaddawg via AccessMonster.com]

I have a query that uses dates entered on a form frmMemDateReport in the
boxes txtBeginDate and txtEndDate. The unbound txt boxes have a short date
input mask and short date format.

Here's the query language I'm trying to use (in design view):

=[Forms]![frmMemDateReport]![txtBeginDate] And <=[Forms]![frmMemDateReport]![txtEndDate]

as Criteria for the StartDate field in a table (also in short date format).

I have the form open with dates in it and run the query, I get no results.
If I add an OR line to the StartDate field and put in the same dates (as on
the form) with Between...And... I get the proper results.

I know this is hard to diagnose with this information, but am I missing
something obvious? Is there some sort of formatting issue? I've tried to
format all dates "yyyymm" to no avail. I tried Between...And with the form
reference but that was a disaster.

Thanks for your help.

 

[John Spencer]

Try forcing the data type with CDate

=CDate([Forms]![frmMemDateReport]![txtBeginDate]) And <=CDate([Forms]![frmMemDateReport]![txtEndDate])

Another way to handle this is to declare the parameters in the query. In SQL View

Parameters [Forms]![frmMemDateReport]![txtBeginDate] DateTime,
[Forms]![frmMemDateReport]![txtEndDate] DateTime;
SELECT ...

I have a query that uses dates entered on a form frmMemDateReport in the
boxes txtBeginDate and txtEndDate. The unbound txt boxes have a short date
input mask and short date format.

Here's the query language I'm trying to use (in design view):

=[Forms]![frmMemDateReport]![txtBeginDate] And <=[Forms]![frmMemDateReport]![txtEndDate]

as Criteria for the StartDate field in a table (also in short date format).

I have the form open with dates in it and run the query, I get no results.
If I add an OR line to the StartDate field and put in the same dates (as on
the form) with Between...And... I get the proper results.

I know this is hard to diagnose with this information, but am I missing
something obvious? Is there some sort of formatting issue? I've tried to
format all dates "yyyymm" to no avail. I tried Between...And with the form
reference but that was a disaster.

Thanks for your help.

 

[quaddawg via AccessMonster.com]

Thanks for responding.
Sorry to be dense, but something is still amiss. Using the CDate function
got hits, but too many; the query results did not reflect the limits on the
form, like it was an Or operator rather than an And operator. Ideas?
Lawton

Try forcing the data type with CDate

=CDate([Forms]![frmMemDateReport]![txtBeginDate]) And <=CDate([Forms]![frmMemDateReport]![txtEndDate])

Another way to handle this is to declare the parameters in the query. In SQL View

Parameters [Forms]![frmMemDateReport]![txtBeginDate] DateTime,
[Forms]![frmMemDateReport]![txtEndDate] DateTime;
SELECT ...

I have a query that uses dates entered on a form frmMemDateReport in the
boxes txtBeginDate and txtEndDate. The unbound txt boxes have a short date

[quoted text clipped - 14 lines]

Thanks for your help.

 

[John Spencer]

Are your dates in US format of Month Day Year?

As an experiment
--Close the form
--Run the query and when prompted for dates enter them in yyyy/mm/dd format.
Do you get correct results? If so, then it looks as if you may have a
problem with the date strings.


See the following for a discussion of date formats, etc.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

Thanks for responding.
Sorry to be dense, but something is still amiss. Using the CDate function
got hits, but too many; the query results did not reflect the limits on
the
form, like it was an Or operator rather than an And operator. Ideas?
Lawton

Try forcing the data type with CDate

=CDate([Forms]![frmMemDateReport]![txtBeginDate]) And
<=CDate([Forms]![frmMemDateReport]![txtEndDate])

Another way to handle this is to declare the parameters in the query. In
SQL View

Parameters [Forms]![frmMemDateReport]![txtBeginDate] DateTime,
[Forms]![frmMemDateReport]![txtEndDate] DateTime;
SELECT ...

I have a query that uses dates entered on a form frmMemDateReport in the
boxes txtBeginDate and txtEndDate. The unbound txt boxes have a short
date

[quoted text clipped - 14 lines]

Thanks for your help.

 

[quaddawg via AccessMonster.com]

I think the query and form just weren't playing nicely together while still
under design. I finished up the code for the form and everything seems to be
working now. Keep my fingers crossed.
Thanks again for your help,
Lawton

Are your dates in US format of Month Day Year?

As an experiment
--Close the form
--Run the query and when prompted for dates enter them in yyyy/mm/dd format.
Do you get correct results? If so, then it looks as if you may have a
problem with the date strings.

See the following for a discussion of date formats, etc.
International Dates in Access at:
http://allenbrowne.com/ser-36.html

Thanks for responding.
Sorry to be dense, but something is still amiss. Using the CDate function

[quoted text clipped - 21 lines]