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.

 

[WaltLukeIII]

write a simple macro for the date using if elseif commands

dim statement1, userInput
UserInput = someTextfield
LowerCase (userInput)

If userInput = "december" then
statement1 = "Between #12/1/00# And #12/31/00#"
elseif userInput = "january" then
statement1 = "Between #1/1/00# and #1/31/00#"
...
End If

If i understand your first question correctly then I try basing your report off of a query
Each time the report is run the query is rerun also so the data is automatically updated
If you have any other questions feel free to ask.

Walter III


Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[ruthcali]

Hi,
thanks for responding.
i need help with some syntax. i think i am close.

i made a table called tblMonths with 3 columns:
Month From TO
January January 1 January 31
etc.

then i created a form called frmRptTotals and on the form i made a list box called cboMonth with the 3 columns above. The 1st column is visible and the other 2 are 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: =[cboMonth].[Column](2) & ", " & [cboYear]

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

But, everything turns up 0! help

i have a feeling there is a problem with my syntax. do i need to type ...between & "'" & Forms![frmRptTotals]!...

thanks,
ruth