generate multiple reports based on queries with changing criteria

[craig1988]

Hi all



This is the finishing piece to a project I am currently working on.

I have a table with 5 fields (tblMain); AccNo, AccDesc, SpecialPrint, ChequeDate, ChequeVolume

The Cheque dates can vary from 1984 to current date and beyond (as time goes by).

I need to generate a separate report for each year that is contained in the table. (it is generally only 5 or 6 but I will have to allow for every year from 1984 onwards). The report, I presume will be based on a SELECT query. I also presume I need some sort of a loop to iterate through the years?

Can someone get me started?

Regards
Craig

[June7]

You can either print out a separate report for each year or have one report print with page break for each year. The first requires looping a recordset object in VBA, the second is accomplished with report design.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Year([ChequeDate]) AS Yr FROM tablename;")
rs.MovFirst
While Not rs.EOF
DoCmd.OpenReport "report name", , , "Year([ChequeDate])=" & rs!Yr
DoCmd.Close acReport, "report name" , acSaveNo
rs.MoveNext
Wend

[craig1988]

You can either print out a separate report for each year or have one report print with page break for each year. The first requires looping a recordset object in VBA, the second is accomplished with report design.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Year([ChequeDate]) AS Yr FROM tablename;")
rs.MovFirst
While Not rs.EOF
DoCmd.OpenReport "report name", , , "Year([ChequeDate])=" & rs!Yr
DoCmd.Close acReport, "report name" , acSaveNo
rs.MoveNext
Wend

I like this code. I am making progress however I am getting an error with the below piece of code


"Year([ChequeDate])=" & rs!Yr

Can you see an error in this??

[June7]

No. What is the exact error message?

You need to use your real table name in place of tablename.

[craig1988]

Hi June7

Thanks for the continued support.

This is the error I get!!

Run-time error ‘3075’

Syntax error (missing operator) in query expression ‘Year([ChequeDate])=’


Here is my full code:

Private Sub Command52_Click()
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Year([ChequeDate]) AS Yr FROM tblIBISSheets;")
    
    rs.MoveFirst
    While Not rs.EOF
    
    DoCmd.OpenReport "rptTest", acViewReport, , "Year([ChequeDate])=" & rs!Yr
    DoCmd.Close acReport, "report name", acSaveNo
    rs.MoveNext
    
    Wend
End Sub

I have it triggered from a test button for now, hence the "Command52_Click()". I have all tables, reports etc spelled correct.


Thanks
Craig

[June7]

I don't know why that fails. The syntax works for me.

Step debug. Follow code as it executes. What value is rs!Yr holding? Refer to link at bottom of my post for debugging guidelines.

About working with recordsets http://allenbrowne.com/ser-29.html

[craig1988]

Hi June7

It was a formatting issue. I should have spotted it. The code runs fine now. Thank you!!