DoCmd.OpenReport Error

[dvarvel]

I am trying to export a single report to multiple pdfs. I think I got the Loop part working, but I can't figure out why I'm getting the attached error. I am ignorant when it comes to VBA, I only know what I have taught myself and learned in these forums. Any help would be appreciated.

[Gasman]

Debug.print strWhere and you should see your error.

Your SQL string is incorrect.

Edit: You have already?
It should either have = or IN not both

[pbaldy]

You need single quotes around the state value, since it's text.

[Gasman]

Originally Posted by pbaldy

You need single quotes around the state value, since it's text.

Oops, I *thought* that was part of the SQL statement?

Sorry dvarvel

[dvarvel]

Thanks, Gasman, but I don't understand what you mean by "It should either have = or IN not both".

[pbaldy]

I thought there was an incomplete In() clause at first too. IN is the abbreviation for a US state, so with the field name I'm guessing that's the problem.

[Gasman]

Originally Posted by dvarvel

Thanks, Gasman, but I don't understand what you mean by "It should either have = or IN not both".

I didn't realise it was the state abbreviation for Indiana?
You can have SQL along the lines of WHERE State IN ("IN","LA","CA") and it would return for those three states.

[dvarvel]

I want the code to loop through the entire query and create a separate report for every state. I have all 50 states in the report, not just Indiana. I don't know why it is getting stuck with IN.

[pbaldy]

Based on what you're doing, I think you want to end up with:

State = 'IN'

[dvarvel]

pbaldy - I changed: strWhere = LoopedField & " = " & LoopedFieldValue
TO:
strWhere = LoopedField & LoopedFieldValue


Now it is asking me to Enter Parameter Value for StateIN

That wasn't it. lol - Still trying to figure it out. I think it has to do with putting single quotes around the LoopedFieldValue.

[dvarvel]

Ok. I got it to output a pdf, but it only saved the state of IN. It should loop through the entire query and save a report for each state.

[Minty]

Post up the whole actual code you are using , press the # in the editor to put it into code tags to make it readable.

[pbaldy]

What is the code now? Are all the states in the "Domain" table/query?

[dvarvel]

I finally got it to work. Now, the problem is that since the query has each state listed multiple times, the loop is looking at each line and creating a report for based on the state on that line. My query can have up to 50k lines O.O!!
example:
Line 1 is state IL so it creates a report with all of the IL information in the query.
Line 2 is state MO so it creates a report with all of the MO information in the query.
Line 3 is state IL so it creates a report with all of the IL information in the query and over writes the current IL report.

Here is my code:
Const Folder = "C:\Test"
Const Domain = "Retail Sales - For Report - TEST"
'Domain can be table name, query name, or sql statement that provides the values to loop
Const LoopedField = "State"
Const ReportName = "Monthly Tax Report - TEST"

Dim rs As DAO.Recordset
Dim LoopedFieldValue As String
Dim FileName As String
Dim FullPath As String
Dim strWhere As String
Set rs = CurrentDb.OpenRecordset(Domain)

Do While Not rs.EOF
LoopedFieldValue = rs.Fields(LoopedField)
FileName = LoopedFieldValue & ".PDF"
'The field may be a text field. It then must be surrounded in singlequotes. If so uncomment below
'LoopedFieldValue = "'" & LoopedFieldValue & "'"
FullPath = Folder & FileName
strWhere = LoopedField & "=" & "'" & LoopedFieldValue & "'"

Debug.Print FullPath
Debug.Print strWhere
DoCmd.OpenReport ReportName, acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
DoCmd.Close acReport, ReportName
rs.MoveNext
Loop

[pbaldy]

Try this:

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT State FROM " & Domain)