Date range for report

[pwilly]

I have created a database in Access 2000 file format that tracks something similar to helpdesk calls. I need a report that will display all of the new calls opened and all of the calls closed within a 6 month period. This report will be ran from now on, twice a year covering Jan to the end of June and again for July to the end of the year. The data needed in the report all lies within 2 different columns in a single table. One column is named CallType and the other is Closed. I used an Option group to create the control on the form to enter data entry for both. So depending on what type you check on the form it enters a specific number that represents that type in the CallType column and for the Closed column I have another Option group with the default being No (not closed) which is represented by a 0 in the table and if it has been closed, the field value is 1 for that record.

I need a report that will list each type of call by name, not by the number stored in the database, how many calls were opened in the 6 month period for each type and how many were closed for each type in the same period. So far I have created the following 2 queries for each call type:

[FONT=Times New Roman][SIZE=3]query open 5[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]SELECT Caller.CallType[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]FROM Caller[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]WHERE Caller.CallType=5 And Caller.[Date of Call] BETWEEN #1/1/2009# AND #6/30/2009#;[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]query closed 5[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]SELECT Caller.Closed[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]FROM Caller[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]WHERE Caller.CallType=5 And Caller.Closed=1 And Caller.[Date of Call] BETWEEN #1/1/2009# AND #6/30/2009#;[/SIZE][/FONT]

I have created a report that simply uses labels to list the name of each type and beside the label name I have a text box that shows the total number of open calls by doing a =DCount("*", "[query open 5]"). I have another text box next to it that shows the total number of closed calls.



Call Type Open Closed
Information 29 25

My problem is I will have to manually go in every 6 months and change the dates in each query for the next reporting period.

I have seen posts that describe using a form with unbound controls that allow you to enter a From date and a To date to search a range of dates and returns all data that falls within that range to a report. That would be perfect but what I ran into is that I don't just want raw data returned, (like names that were entered within that date range) I need a count of the total number of type 5 calls that were received within that data range in one column of the report as well as the total number of type 5 calls that were closed within that data range in another column (and type 1-4 as well).

I am finding this very difficult to do. Any idea of how to go about this? Is there a better way to do this? Please keep in mind that I am not a programmer so I sort of need specific instructions. Any help would be most appreciated. Sorry for the long post but I wanted make sure I was understood to avoid wasting your time.

 

[raskew]

Hi -

You can use a parameter query. Here's an example from one of my own tables:

Parameters [Enter Start Date] datetime;
SELECT
    Item
  , startDate
  , endDate
FROM
   tblDevProcess
WHERE
   (((tblDevProcess.startDate) Between [Enter Start Date] 
AND
   (DateAdd("m",6,[Enter Start Date])-1)));

When run, it'll prompt once with [Enter Start Date], it'll then create a date range between the date you entered and that date plus 6 months minus 1 day. So, if you entered 7/1/2009, it'd return just those records with a StartDate between #7/1/2009# and #12/31/2009#.

There'd be no need to mess with actual dates in the query.

HTH - Bob

 

[Brianwarnock]

I personally prefer to use a form to input dates but you can still use Raskews technique to avoid hardcoding and entering 2 dates.

However that does not appear to be your only problem. I thing that you can do all you require in one query.

As you only have 5 types of call use a nested IIf to convert the numeric type to the name. The preferred option is a VBA function using Select Case or Switch, but save that for the future.
Then to do your counts useing new field Countof Closed:IIf(Closed=1,1) and Countofopen:IIf(Closed=0,1)
and place your criteria for the date field
Convert to Totals query
Group by on Type , Where for the Date and Sum the other 2 fields.

Brian

Brian