Hide query result from chart

[Guest]

I have created a chart on an Access report which is based on start date and
end date input from a form.
I have nested queries behind the scenes which pull the correct records based
on the dates.
Then I use this output to group the records into quarters. I have used the
following expression to count the number of records in each quarter - Qtr103:
Sum(IIf([DateMatterServed] Between #1/1/2003# And #3/31/2003#,1,Null)).
The report is based on the output of this query.
The user would like to have the capability of looking at two years of
quarters at a time, so all quarters from 2003 - 2004 are set up in the query.
The problem I am having is if the user enters 1/1/04 and 12/31/04 as the
dates, the 2003 quarters still display on the report. Is there a way to
ensure only the quarters in the specified date range display on the report?

I am using Access 2000.

Many thanks for any help you can provide.

 

[[MVP] S.Clark]

In the WHERE clause, restrict the data to only the desired time period.

 

[Guest]

My first query does restrict the time period in the WHERE clause as shown
below.
SELECT Forums.Category, AdminCharges.CaseNumber, AdminCharges.DateMatterServed
FROM Forums INNER JOIN AdminCharges ON Forums.Forum =
AdminCharges.CurrentForum
WHERE (((Forums.Category)="admin") AND ((AdminCharges.DateMatterServed)
Between [forms]![frmChartInput]![StartDate] And
[forms]![frmChartInput]![EndDate]));

I then take this query and use it as the basis for the next query, which
separates the data into the different quarters. I then use this query as the
basis for the chart.

SELECT qryAdminChargeCount1Chart.Category, Sum(IIf([DateMatterServed]
Between #1/1/2003# And #3/31/2003#,1,Null)) AS Qtr103,
Sum(IIf([DateMatterServed] Between #4/1/2003# And #6/30/2003#,1,Null)) AS
Qtr203, Sum(IIf([DateMatterServed] Between #7/1/2003# And
#9/30/2003#,1,Null)) AS Qtr303, Sum(IIf([DateMatterServed] Between
#10/1/2003# And #12/31/2003#,1,Null)) AS Qtr403, Sum(IIf([DateMatterServed]
Between #1/1/2004# And #3/31/2004#,1,Null)) AS Qtr104,
Sum(IIf([DateMatterServed] Between #4/1/2004# And #6/30/2004#,1,Null)) AS
Qtr204, Sum(IIf([DateMatterServed] Between #7/1/2004# And
#9/30/2004#,1,Null)) AS Qtr304, Sum(IIf([DateMatterServed] Between
#10/1/2004# And #12/31/2004#,1,Null)) AS Qtr404, Sum(IIf([DateMatterServed]
Between #1/1/2005# And #3/31/2005#,1,0)) AS Qtr105,
Sum(IIf([DateMatterServed] Between #4/1/2005# And #6/30/2005#,1,Null)) AS
Qtr205, Sum(IIf([DateMatterServed] Between #7/1/2005# And
#9/30/2005#,1,Null)) AS Qtr305, Sum(IIf([DateMatterServed] Between
#10/1/2005# And #12/31/2005#,1,Null)) AS Qtr405
FROM qryAdminChargeCount1Chart
GROUP BY qryAdminChargeCount1Chart.Category;

Is there an easier way to do this?

Thank you in advance for any help you can provide.