SQL Query, Date Parameter troubles

[gRegulator]

Hi,

I have a union query, qry_offenders based upon tbl_offenders, and is written as follows:

SELECT 'Probation' AS ConditionDescription, Male, Female, [Probation Start] as [Start]
FROM tbl_Offenders WHERE Probation = True

UNION ALL SELECT 'Conditional Sentence', Male, Female, [CS Start] as [Start]
FROM tbl_Offenders WHERE [Conditional Sentence] = True

UNION ALL 
SELECT 'Fine Option Program', Male, Female, 
       NZ([Probation Start],[CS Start]) As [Start]
FROM tbl_Offenders WHERE [Fine Option Program] = True

UNION ALL SELECT 'Community Service', Male, Female, 
       NZ([Probation Start],[CS Start]) As [Start]
FROM tbl_Offenders WHERE [Community Service Order] = True;

That works fine.

I then have a summary query on which i have placed a date parameter. However, the results of the date parameter never seem to be accurate. I am wondering if there is something wrong with my code, or if someone could give me a bit of advice.

Here is my code in the summary query:

SELECT qry_offenders.ConditionDescription AS Disposition, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_offenders
WHERE (((qry_offenders.Start) Between [Forms]![frm_dateparameter_q27]![txtStart] And [Forms]![frm_dateparameter_q27]![txtEnd]))
GROUP BY qry_offenders.ConditionDescription;

I really appreciate any help, Thanks!

 

[genomon]

Since you are geting the date parameters from a form, make sure they are properly formatted with the "#" character before and after the date (for Access - SQL server uses the single quote char).

 

[gRegulator]

Hi,

I think I fugured it out... I put the a line of code above the previous version of the summary query to read:

PARAMETERS [Forms]![frm_dateparameter_q27]![txtStart] DateTime, [Forms]![frm_dateparameter_q27]![txtEnd] DateTime;
SELECT qry_offenders.ConditionDescription AS Disposition, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_offenders
WHERE (((qry_offenders.Start) Between [Forms]![frm_dateparameter_q27]![txtStart] And [Forms]![frm_dateparameter_q27]![txtEnd]))
GROUP BY qry_offenders.ConditionDescription;

and that worked...thanks for your help!