Printing Monthly Reports from Database

[Mark B]

I am new to MS Access and am running into difficulty trying to figure out how to make a monthly report. I have built a small database with 7 fields;IE Job#,Job type,Date,& some job info. I am unfamiliar with queries or filtering. I would like the input form to have a spot to generate a report for an input month & year. I would really appreciate any help you could offer.
Thanks, Mark

[This message has been edited by Mark B (edited 02-12-2002).]

 

[The_Doc_Man]

You might do best playing around with a query based on the DateAdd function. Read up on this in the Help files including the examples.

If you run your report for the previous month on the first day of this month, you would base that report on a query selecting on your date field using a criterion that looks something like

>=DateAdd("m", -1, Date())

On the first day of a month, the above should return a date equal to the first day of the previous month. And that IS the 'greater than or equal' operator in front of that clause.

If you didn't want to include today (because it is the first of this month), then select the date TWICE in your query and make the second date's criterion (on the same line as the first criterion)

<Date()

That IS the 'less than' operator in front of the Date function.

The SQL for this query (if you look at it that way, and assuming the field name is [Job Date]) should then include something like

... WHERE [Job Date] >= DateAdd("m",-1,Date()) AND [Job Date] < Date() ...


Before anyone asks, this is easier than using BETWEEN ... AND ... because I am assuming Mark B does NOT want to include today's date in last month's report. This is, I think, the EASIEST method to do that. Using BETWEEN would require a second DateAdd function.

 

[Mark B]

Thank you for your feedback Doc Man, I will have to be able to run the report at any time during the month, or maybe even several months after. I've entered a field to automatically store the date the job was closed in hopes to extract the month and year from it for the report. It's a small database so it won't hurt if it has to run a couple more operations to achieve it, as long as it it fairly simple to operate. I would like to have a pop up form opened of an action button on the main form to input the month & year needed on the report and have it sort the database for only those files. Any ideas? I appreciate your help. If I don't get some answers quickly I may not have any hair left.
Thanks again, Mark