Hide query result from chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
Back
Top