Openreport where clause not working

[aparnag]

Hi,

I am trying to create a report for each lender detailing there loans.
I am have a report which is works fine but I wanted to automate the process rather than mannually entering each lender individually.
Here is my code.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
Dim filename As String
Dim ctir As String

strSQL = "Select * FROM looptestqry"
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While rs.EOF = False
filename = rs![LastOfOfficer Name]
ctir = "looptestqry.[LastOfOfficer Name]IN ( & Chr(34) & filename & Chr(34))"
filename = Replace(filename, "\", "-")
DoCmd.OpenReport "Lender Portfolio Report - Officer Level View(For Macro)", acViewReport, , citr, acNormal
DoCmd.OutputTo acOutputReport, "Lender Portfolio Report - Officer Level View", "PDFFormat(*.pdf)", "C:\Reports\" & filename & ".pdf", False, "", , acExportQualityPrint
DoCmd.Close acReport, "Lender Portfolio Report - Officer Level View"
rs.MoveNext
Loop
End Sub

It spits out all the lenders report rather than just giving me the report for the particular lender as selected automatically by the code i,e filename.

I am not sure what is the issue.
Do you think or else I have to set a parameter. But I do not want ot type, is there a way to automatically assign the parameter to the value in the filename.

I am newbie. Appreciate any help.

Thanks,
Aparna

 

[Alansidman]

What happens when you run the query from the QBE. Suggest you do that then check the SQL statement that Access creates when you have it working and compare it to your VBA/SQL code.

Alan