How to create a summary report for a specific month

[ruthcali]

I am using Access97.


i have to produce a summary report showing data for a specific month.

for example,
how many projects were created in december,
how many projects were cancelled in december,
how many project surveys were completed in december,
etc.

It would be easy if my report only required one item of data. Then i could go into the query and for my project_created_date field i would type "between [enter beg.date] and [enter ending date]"

but, i have many fields that need criteria. i can't use an AND and i can't use an OR because each field needs to be independent of the others.

any other ideas besides creating many different reports and putting the totals manually in Excel?

thanks,
ruth

PS-instead of prompting the user for beg.date and end date, can i do something where the user only enters, for example, 'december' and that gives the data for dec 1 thru dec 31. i need to do this report every month.

 

[DougP]

This works most of the time
In your query put the follow in your date criteria
Between date1() And date2(0)

Create a new module and paste both of these functions in it and save it
--------------
Public Function Date1()
Dim retval As Variant
retval = InputBox("Enter Date Number like 10=Oct or 12=Dec", "Enter Date", 0)
Date1 = DateSerial(Year(Now), 12, 1)
Call Date2(retval)
End Function

Public Function Date2(Month1)
Date2 = DateSerial(Year(Now), Month1 + 1, -1)
End Function
--------------------------
So when you run your query you will be prompted for the month number. 1 = january, 12 = december
then the two functions will create the 2 dates such as 12/1/00 and 12/30/00
Note that it does not seem to get 31 for December.
A little tweaking will whip it into shape.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[ruthcali]

Hi Doug, thanks for writing.

i ended up doing the following:

i made a table called tblMonths with 2 columns:
Month From
January 01/01
February 02/01
etc.

then i created a form called frmRptTotals and on the form i made a list box called cboMonth with the 2 columns above. The 1st column is visible and the other one is invisible.

Then i made another list box called cboYear where i entered years (using Value list).

Then i made a text box called txtDate1 with the value: =[cboMonth].[Column](1) & "/" & [cboYear]

then i made another text box called txtDate2 with the value: =DateAdd("d",-1,DateAdd("m",1,[txtDate1]))

Then in my report, i have a text box:
=IIf([project_issued_date] Between [Forms]![frmRptTotals]![txtDate1] And [Forms]![frmRptTotals]![txtDate2],1,0)

 

[Quehay]

ruthcali,

Don't know if you're still interested in this problem. To get tallies for a given time period that is a calendar week or month (rather than a date range, e.g., June 7 - July 12)
use date part as the WHERE clause in a SQL Aggregate SUM query (the field [ReportDate] is shown twice and aliased the first time as "CntRpts&quot:

[tt]

Summary Query "qry_ReportsMnth_cnt"

CntRpts:ReportDate | ReportDate
|
Count | WHERE
DatePart(Date(),"mm&quot

I forget the proper syntax for DatePart but this should give you the idea. It's a great way to get weekly,month, or daily totals of fluctuating data.[/tt]