crystal reports query

0
votes

This is my current query to pull data out of an Access Database:

SELECT Count(Master.MasterID) AS Logs
FROM Master
WHERE (((Master.SubmitDate)<=#5/01/2010#) AND (((Master.CompleteDate)>=#5/01/2010# Or (Master.CompleteDate) Is Null)));

Currently I have to change the date for each month I want to find the count for. I am trying to migrate this to a crystal report and am having a really difficult time of it. I want to be able to put in a start date and an end date of one year and have the report break down in the format of the query above so that it shows the count of logs open at the beginning of every month.

1
crystal-reports
Why bother migrating the queries? You can just declare your Crystal Report's source database to be the Access file. - PowerUser
my source database IS the access file. this is a query in the database that requires I change the hard coded dates for each month I want to track...ie i have to change the query 12 times to get data for each month. I am asking how I can replicate this in crystal reports using a full years date range as my primary input source. - ecathell

1 Answers

1
votes
  1. Create new parameter fields {?Start Date} and {?End Date} in the Crystal Reports Designer.
  2. Remove the date selection criteria from your query.
  3. Either group by submit date and complete date in your query, or select * from master.
  4. Use the select expert in the Crystal Reports Designer to set up similar record selection conditions to the one above, based on the parameters {?Start Date} and {?End Date}.
  5. Add a summary field (either sum of logs, if you grouped in step 3, or a record count if you selected * from master) to the report footer.