Need help on Run-time error 3129 with SELECT statement-CodePudding

Code is below. Getting errors on first and last lines. I think there is an extra ( or else missing ) on 2nd line but that wouldn't explain the error on the last line.

DoCmd.RunSQL (sqlStr)

strSQL = "SELECT fct_monitoring.MeasureID FROM fct_monitoring WHERE MeasureID IN (" & _
    Chr(34) & "CAUTI_Rate_All" & Chr(34) & "," & _
    Chr(34) & "CDI_LabID" & Chr(34) & "," & _
    Chr(34) & "CLABSI_Rate_All" & Chr(34) & "," & _
    Chr(34) & "Falls_Injury" & Chr(34) & "," & _
    Chr(34) & "READ-1" & Chr(34) & ")" & _
    Chr(34) & "CAUTI_SIR_All" & Chr(34) & "," & _
    Chr(34) & "CDI_SIR" & Chr(34) & "," & _
    Chr(34) & "CLABSI_SIR_All" & Chr(34) & "," & _
    " GROUP BY fct_monitoring.MeasureID;"

Set msrRst = dbs.OpenRecordset(strSQL)

CodePudding user response：

You really want to just print out the sql statement - it is too error prone to try to work out all these concatenations.

strSQL = "SELECT fct_monitoring.MeasureID FROM fct_monitoring WHERE MeasureID IN (" & _ 
    Chr(34) & "CAUTI_Rate_All" & Chr(34) & "," & _ 
    Chr(34) & "CDI_LabID" & Chr(34) & "," & _ 
    Chr(34) & "CLABSI_Rate_All" & Chr(34) & "," & _ 
    Chr(34) & "Falls_Injury" & Chr(34) & "," & _ 
    Chr(34) & "READ-1" & Chr(34) & ")" & _ 
    Chr(34) & "CAUTI_SIR_All" & Chr(34) & "," & _ 
    Chr(34) & "CDI_SIR" & Chr(34) & "," & _ 
    Chr(34) & "CLABSI_SIR_All" & Chr(34) & "," & _ 
    " GROUP BY fct_monitoring.MeasureID;"

Debug.Print(sqlStr)

With that debug output you can look at something that makes a bit more sense (it should be a sql statement you can read better - if you want, you can even paste it into a query in sql design view and run it.

Although actually in this better format, I think I can see that probably after READ-1 you have a parenthesis that should be a quote, and after CLABSI_SIR_All you have a comma where you should have a parenthesis (this looks like someone decided to move a line around).

CodePudding user response：

Not possible to be sure from your posted code exactly what you want to do, but this should maybe be close:

strSQL = "SELECT distinct fct_monitoring.MeasureID " & _
         "   FROM fct_monitoring WHERE MeasureID IN " & _
         "('CAUTI_Rate_All','CDI_LabID','CLABSI_Rate_All','Falls_Injury', " & _
         " 'READ-1','CAUTI_SIR_All','CDI_SIR','CLABSI_SIR_All')"

No need for all that Chr(34) since you can use single-quotes.