none
Compile error - Expected: End of Statement

    Question

  • Hi All,

    I'm trying to adapt my query sql to use in vba & my brain is closing down. I keep getting the compile error: Expected: End of statement. The last time I encountered this message I was able to figure out the quotes but this is a different syntax using Dlookup in the sql string.

    Can someone help me past this brain blockage (can't see the wood for the trees comes to mind)

    Here is the code string

    strSQL = "UPDATE [tblResultsImported] " _
    strSQL = strSQL & "SET tblResultsImported.CouplesInClass = DLookUp("CountOfClass","qryImportClassCount","class = '" & [class] & "'");"

    Wednesday, September 22, 2010 11:04 PM

Answers

  • Either remove the  _  continuation from the first row, or make it all one continuation; and remember that each open quote will require a matching close quote. The " before CountOfClass is being read as the closing quote for the one before SET.

    Try

    strSQL = "UPDATE [tblResultsImported] SET tblResultsImported.CouplesInClass = " _
     & "DLookUp(""CountOfClass"",""qryImportClassCount"",""class = '"" & [class] & ""'"");"

    using the fact that you can include a doublequote in a doublequote delimited string by using a doubled doublequote (a fine example of doubletalk!)

     


    John W. Vinson/MVP
    Wednesday, September 22, 2010 11:21 PM

All replies

  • Either remove the  _  continuation from the first row, or make it all one continuation; and remember that each open quote will require a matching close quote. The " before CountOfClass is being read as the closing quote for the one before SET.

    Try

    strSQL = "UPDATE [tblResultsImported] SET tblResultsImported.CouplesInClass = " _
     & "DLookUp(""CountOfClass"",""qryImportClassCount"",""class = '"" & [class] & ""'"");"

    using the fact that you can include a doublequote in a doublequote delimited string by using a doubled doublequote (a fine example of doubletalk!)

     


    John W. Vinson/MVP
    Wednesday, September 22, 2010 11:21 PM
  • John you're a wizard ..!!

    Thanks

    Thursday, September 23, 2010 5:06 AM