Date Parameter on Crosstab Query 1

[gRegulator]

Hi,

I am trying to figure out how to put date parameters on a crosstab query, but am having no luck. Below is the SQL I have used to create my union query which I have called qry_Union_q30_Age_Disposition:

SELECT 'Probation' AS ConditionDescription, qry_AgeGroups.[Probation Start] AS [Start], [Age Group]
FROM qry_AgeGroups
WHERE [Probation]=True

UNION ALL SELECT 'Conditional Sentence', qry_AgeGroups.[CS Start] AS [Start], [Age Group]
FROM qry_AgeGroups
WHERE [Conditional Sentence]=True

UNION ALL SELECT 'Fine Option Program',
NZ([Probation Start],[CS Start]) As [Start], [Age Group]
FROM qry_AgeGroups
where [Fine Option Program] = true

UNION ALL SELECT 'Community Service Order', 
NZ([Probation Start],[CS Start]) As [Start], [Age Group]
FROM qry_AgeGroups
where [Community Service Order] = true;

I then built a crosstab query from this, which has the following code:

TRANSFORM Count(qry_Union_q30_Age_Disposition.Start) AS CountOfStart
SELECT qry_Union_q30_Age_Disposition.ConditionDescription, Count(qry_Union_q30_Age_Disposition.Start) AS [Total Of Start]
FROM qry_Union_q30_Age_Disposition
GROUP BY qry_Union_q30_Age_Disposition.ConditionDescription
PIVOT qry_Union_q30_Age_Disposition.[Age Group];

Is there a way to put a date parameter on this so it will filter those records from a desired date range? I would prefer to have the dates populated from a form with text fields [Forms]![frm_dateparameter_q30]![txtStart] and [Forms]![frm_dateparameter_q30]![txtEnd].

I hope this is clear to everyone, and I appreciate all help!

Thanks in advance

 

[PHV]

PARAMETERS [Forms]![frm_dateparameter_q30]![txtStart] DateTime, [Forms]![frm_dateparameter_q30]![txtEnd] DateTime;
TRANSFORM Count([Start]) AS CountOfStart
SELECT ConditionDescription, Count([Start]) AS [Total Of Start]
FROM qry_Union_q30_Age_Disposition
WHERE [start] Between [Forms]![frm_dateparameter_q30]![txtStart] And [Forms]![frm_dateparameter_q30]![txtEnd]
GROUP BY ConditionDescription
PIVOT [Age Group];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gRegulator]

Great, Thanks PHV that helped so much!