Query Generating different results

[jj1]

If I use this query I get the results what I want
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet


GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null))
ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;

But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query. Can anybody help me what wrong I am doing.

SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause
FROM tblMainTWTTPSheet
WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate]))
GROUP BY tblMainTWTTPSheet.txtRootCause
HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));

[rpeare]

Your first query isn't checking the date at all so how is it giving you the results you want if you want to apply date criteria to your query? Nor is the query ordered the same way.

Are you sure your fields on your form as in a date format? Are you generating this SQL statement dynamically or do you actually have a saved query? Are you sure the 'date' field in your table tblMainTWTTPSheet is being stored as a date and not as a text value?

I suspect there is a disconnect between how the date value is being stored and the date criteria (by the way it's poor practice to use reserved words for field names like 'date', 'year', 'month', 'control', etc).

you may be able to try criteria like


between "#" & forms!frmstratificationofrootcauses!startdate 7 "#" and "#" & forms!frmstratificationofrootcauses!enddate & "#"

This would force your start date and end date to be considered dates if they are currently being considered strings

[jj1]

Its didn't solve my problem. It has date as date/time field in my main table.

between "#" & forms!frmstratificationofrootcauses!startdate 7 "#" and "#" & forms!frmstratificationofrootcauses!enddate & "#" gives error query is complex to be evaluated.

Your first query isn't checking the date at all so how is it giving you the results you want if you want to apply date criteria to your query? Nor is the query ordered the same way.

Are you sure your fields on your form as in a date format? Are you generating this SQL statement dynamically or do you actually have a saved query? Are you sure the 'date' field in your table tblMainTWTTPSheet is being stored as a date and not as a text value?

I suspect there is a disconnect between how the date value is being stored and the date criteria (by the way it's poor practice to use reserved words for field names like 'date', 'year', 'month', 'control', etc).

you may be able to try criteria like


between "#" & forms!frmstratificationofrootcauses!startdate 7 "#" and "#" & forms!frmstratificationofrootcauses!enddate & "#"

This would force your start date and end date to be considered dates if they are currently being considered strings

[rpeare]

If you cut and paste that you have a 7 where there should be an & mark right after the startdate

[jj1]

Ya I figured that out