Print Monthly Report

[IpeXeuS]

Hello there,

I've one question to ask here. Ok is there way to print monthly report from database records. Let's say that I've listbox where user can select month and preview report from records in that month. Also is it possible that application will automatically add new month to listbox when there's record in that month? So now I ask your help with this, what's easiest way to do this? All advices are approciated, thanks.

 

[Dennisk]

create a query to obtain all the distinct occurance of month , and all occurance of year (otherwise you will display data for that month for all years.)

As new records are added to the db they will appear in your query.

 

[shudini]

Just to expound on Dennisk's solution, there are a few steps to accomplish what you are asking.

Presumably there is a field with the date the new record is added. To extract the distinct months and have them returned in a sorted order, you would need a query that would resemble the following

SELECT Format([fldDate],"mmmm") & " " & Format([fldDate],"yyyy") AS fldMonth
FROM emplAct
GROUP BY Format([fldDate],"mmmm") & " " & Format([fldDate],"yyyy"), Year([fldDate]), Month([fldDate])
ORDER BY Year([fldDate]), Month([fldDate]);

Save this query as MonthsForReport.

On your form that contains the listbox, bind the listbox to the MonthsForReport query.

I'm assuming you've already created the report you want to use without any filters. If this is the case, you would need to add the following code somwhere on the form with the listbox, either to a command button, or to the double-click event of the lisbox:

DoCmd.OpenReport "MyReport",acViewPreview,,"Format([fldDate],"mmmm") & " " & Format([fldDate],"yyyy")=" & listBox.Value