Compile Errors

[NEWER USER]

I have some SQL lines of code that fail in vb but work in SQL View Query.
Failing at the quotations.

DoCmd.RunSQL "UPDATE [Update Template] SET [Update Template].F11 =
Mid([F10],4,InStr([F10]," ")-1) " & _ 'it fails @ ")-1)"
WHERE (((InStr([F10],"/"))=0))" 'Possibly here too "/"


DoCmd.RunSQL "UPDATE tblSales SET tblSales.[Part Number] =
Replace(Replace([Part Number],"-",""),".","")" 'it fails "-"."")

How do I correct? Any help appreciated.

 

[John Spencer]

Double up the quotes when you want a single quote in the resulting string.

Dim strSQL as String
strSQL = "UPDATE [Update Template] " & _
" SET [Update Template].F11 = Mid([F10],4,InStr([F10],"" "")-1) " & _
" WHERE (((InStr([F10],""/""))=0))"

strSQL = "UPDATE tblSales " & +
"SET tblSales.[Part Number] = " & _
"Replace(Replace([Part Number],""-"",""""),""."","""")"

I almost always assign my query string to a variable. That way I can use
Debug.print strSQL
and examine the resulting string for errors. Once I am satisfied that I am
generating a valid SQL string then I can comment out the Debug.Print.

This might help:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

Newer User -

You need the text delimeter (quote), and to put any calculation outside of
quotes. Try this:

DoCmd.RunSQL "UPDATE [Update Template] SET [Update Template].F11 = '" &
Mid([F10],4,InStr([F10]," ")-1) & "'" & _
WHERE (((InStr([F10],"/"))='0'))"

DoCmd.RunSQL "UPDATE tblSales SET tblSales.[Part Number] = '" & _
Replace(Replace([Part Number],"-",""),".","") & "'"

A good way to work through these problems is to create a string variable
that you build the query in (e.g. Dim strSQL). Then you can add a debug
step in your code to see what you have built before trying to run the SQL.
Like this:

Dim strSQL as String
strSQL = "UPDATE tblSales SET tblSales.[Part Number] = '" & _
Replace(Replace([Part Number],"-",""),".","") & "'"

debug.print strSQL

DoCmd.RunSQL strSQL