Query link to Form (Date Range)

[learning_codes]

Hi,

I need your help. I have three queries that are exacted same
setting. The total number of records in three queries are the same
(1,500). Two of three queries link to the form that has the date
range input. I entered from 01/01/2006 and 12/31/2006 in the text box
on the form that link two queries.

I set two of the queries that link to the form:
BETWEEN [Forms]![Menu].txtStartDate and [Forms]![Menu].txtEndDate


The result:

*Query1 - Total: 950
*Query2 - Total: 990
Query3 - Total: 1,500

*Link to the Forms

I should have all three queries should be the same as 1,500.

Is there a reason for that or date format problem ?

YOur help would be much appreciated.

Thanks,

 

[Allen Browne]

Set the Format property of the text boxes to General Date so Access knows
their data type.

Explicitly declare the 2 parameters, so Access knows their type.
Choose Parameters on the Query menu (in query design), and enter 2 rows:
txtStartDate Date/Time
txtEndDate Date/Time

If you apply criteria against calculated date fields, wrap the calculation
in CVDate().

More details:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

If you edit the query by hand, make sure any literal dates are enclosed in #
and in m/d/y format. Details:
http://allenbrowne.com/ser-36.html

This assumes that if you open your table in design view, the field is
Date/Time (not Text.)

It also assumes that you are querying the same data, and that the difference
in results is not a matter of different joins or criteria on other fields,
such as described here:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Hunter57]

Hi,

I need your help. I have three queries that are exacted same
setting. The total number of records in three queries are the same
(1,500). Two of three queries link to the form that has the date
range input. I entered from 01/01/2006 and 12/31/2006 in the text box
on the form that link two queries.

I set two of the queries that link to the form:
BETWEEN [Forms]![Menu].txtStartDate and [Forms]![Menu].txtEndDate

The result:

*Query1 - Total: 950
*Query2 - Total: 990
Query3 - Total: 1,500

*Link to the Forms

I should have all three queries should be the same as 1,500.

Is there a reason for that or date format problem ?

YOur help would be much appreciated.

Thanks,

Can you post the SQL of all three queries? I doubt if you can get much
help without that information being made available. Also, if one of
your queries has no date range criteria and your table contains dates
that are outside of the date range of your other 2 queries, it will
return more records than the other 2 queries.

Hunter57
http://churchmanagementsoftware.googlepages.com

 

[John W. Vinson]

Hi,

I need your help. I have three queries that are exacted same
setting. The total number of records in three queries are the same
(1,500). Two of three queries link to the form that has the date
range input. I entered from 01/01/2006 and 12/31/2006 in the text box
on the form that link two queries.

I set two of the queries that link to the form:
BETWEEN [Forms]![Menu].txtStartDate and [Forms]![Menu].txtEndDate


The result:

*Query1 - Total: 950
*Query2 - Total: 990
Query3 - Total: 1,500

*Link to the Forms

I should have all three queries should be the same as 1,500.

Is there a reason for that or date format problem ?

Yes; presumably there is something different with the queries.

We cannot help you identify the difference between the queries, because you
haven't *shown* us the queries.

Please open the queries in SQL view and post the SQL text here. If you look at
the query datasheets, do you see any conspicuous differences in the records
that are displayed?

John W. Vinson [MVP]

 

[Hunter57]

Hi,

I need your help. I have three queries that are exacted same
setting. The total number of records in three queries are the same
(1,500). Two of three queries link to the form that has the date
range input. I entered from 01/01/2006 and 12/31/2006 in the text box
on the form that link two queries.

I set two of the queries that link to the form:
BETWEEN [Forms]![Menu].txtStartDate and [Forms]![Menu].txtEndDate

The result:

*Query1 - Total: 950
*Query2 - Total: 990
Query3 - Total: 1,500

*Link to the Forms

I should have all three queries should be the same as 1,500.

Is there a reason for that or date format problem ?

YOur help would be much appreciated.

Thanks,

Hi,

Can you post the SQL of your 3 queries? I doubt if you can get much
help until you do. Also, If 2 of your queries have date range
criteria, and the third does not, it will return more records than the
others if your table has any dates that are outside of the date range.

Hunter57
http://churchmanagementsoftware.googlepages.com