More apostophe headaches in StrSQL statement--The dreaded runtime error 3134

[Richard Hollenbeck]

This short little query ought to be as simple as typing it in. I've done
queries like this a thousand times already. For some reason I just can't
seem to get a handle on the mixed usage of quotation marks and apostrophes
in the SQL statements. I have an errorlog table with the time field
automatically filled in as a default value of now(). The key field is an
autonumber called ID.

The other three fields are all I need to fill in. This little piece of code
is tucked into the error handler:

strSQL = "INSERT INTO tblErrorLog (Number, Description, Source) VALUES ( " &
Err.Number & ",'" & Err.Description & "'," & Err.Source & "')"

(but all on one line)

Syntax error in Insert Into statement.

I've been struggling with this for over an hour. What a waste of time! One
apostrophe out of place and the whole thing goes south.

 

[Allen Browne]

You'll find that you run into trouble much less if you use the double-quote
character inside the string instead of the single quote (apostrophe). Just
double-them up within the string.

If you code:
"This string has a "word" in quotes" '<=error!
VBA comes to the quote mark before word, figures the string is ended, and
can't figure out what to do with the rest of the line. The convention is to
double the quotes if they are embedded in quotes, i.e.:
"This string has a ""word"" in quotes"
If the word is at the end of the string, it looks like 3 in a row:
"This string has a ""word"""

Once you understand that, it's all downhill from there.

strSQL = "INSERT INTO tblErrorLog (Number, Description, Source) VALUES ( " &
Err.Number & ", """ & Err.Description & """, """ & Err.Source & """ )"

 

[Guest]

Thanks for this little explaination... Helped me a ton!