Trying to add variables to a string error 3141 1

[vba317]

I am using Access 2003. With extensive help yesterday I have a string that works to run a query. I want to use this query to run multiple times with different information. So I added variables to the string and now i am getting an error 3141 . I am pretty sure I have the quotes messed up somewhere I JUST DON'T KNOW WHERE.

Tom
This code works

strSQL = "TRANSFORM Sum(proc) AS SumOfproc " & _
         "SELECT uci, readingprovname as [rp]  " & _
         "FROM PROC_ReadingProvider " & _
         "WHERE billpd>357 " & _
         "GROUP BY uci, readingprovname " & _
         "PIVOT billpd"

This Code doesn't work the error is on the red line

strFld1 = "proc"
strSel1 = "uci"
strSel2 = "readingprovname "
strTbl = "PROC_ReadingProvider "
strWhr = "billpd"
iWhr = 357

 strSQL = "TRANSFORM Sum(" & strFld1 & ")" & "AS SumOf" & strFld1 & "" & _
         "SELECT " & strSel1 & "," & strSel2 & "" & _
         "FROM " & strTbl & "" & _
         "WHERE " & strWhr & ">" & iWhr & "" & _
         "GROUP BY " & strSel1 & "," & strSel2 & "" & _
         "PIVOT " & strWhr & ""
[red]Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)[/red]

 

[dhookom]

Learn how to use debugging and troubleshooting methods:

strFld1 = "proc"
strSel1 = "uci"
strSel2 = "readingprovname "
strTbl = "PROC_ReadingProvider "
strWhr = "billpd"
iWhr = 357

 strSQL = "TRANSFORM Sum(" & strFld1 & ")" & "AS SumOf" & strFld1 & "" & _
         "SELECT " & strSel1 & "," & strSel2 & "" & _
         "FROM " & strTbl & "" & _
         "WHERE " & strWhr & ">" & iWhr & "" & _
         "GROUP BY " & strSel1 & "," & strSel2 & "" & _
         "PIVOT " & strWhr & ""
debug.Print strSQL
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Check the results of the debug.print by pressing Ctrl+G. I think you might find a serious lack of spaces.

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Thanks Dwayne,

I added some spaces and it works great.

Tom

strSQL = "TRANSFORM Sum(" & strFld1 & ")" & "AS SumOf" & strFld1 & _
         " SELECT " & strSel1 & "," & strSel2 & _
         " FROM " & strTbl & _
         " WHERE " & strWhr & ">" & [iWhr] & _
         " GROUP BY " & strSel1 & "," & strSel2 & _
         " PIVOT " & strWhr & ""