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