Solved Access VBA - Escaping special characters in SQL String criteria.

[ironfelix717]

Back again.

How does one escape special characters, especially the . period/dot operator inside a criteria string?
Topics that exists on this discusses enclosing them with brackets "[ ]". That would be a wonderful solution... but lo and behold that would be far to simple to work in Access, as pointed out here by a commenter:

escaping-special-characters-for-ms-access-query

And proved by myself with several tests.


EXAMPLE:

Criteria = "McHammer.com"
SQL = "SELECT * FROM Users WHERE UserID = '" & Criteria & "';"

^^FAIL!

NOTE: AS described in my last topic, I cannot use a parameter, this must be solved in a string.

Thanks for your help.

 

[cheekybuddha]

How does the above fail? It should work provided you have a UserID = "McHammer.com"

What is the rest of your code?

 

[ironfelix717]

Hi @cheekybuddha

I stand corrected. The dot character will not fail the code. This was a confusion with a code error I had, as described in the previous post regarding escaping CHR(39)

Thanks, sorry for confusion.

Regards

 

[cheekybuddha]

Glad you got it sorted!

Remember it's not too complicated. Just escape the character you choose to use as string delimiter. That is all.

 

[ironfelix717]

Glad you got it sorted!

Remember it's not too complicated. Just escape the character you choose to use as string delimiter. That is all.

I was hoping it was that simple all along. I had my "order of operations" incorrect in my test file so the solutions were not being applied to the final result, and i was consistently getting an error. Dumb mistake. Thanks

 

[Pat Hartman]

This is the answer I posted in your other thread:

To embed quotes in a string can be confusing so I create a constant and save it in a standard module that holds common stuff or utilities so I can copy it from database to database and always have it handy.
Code:Copy to clipboard
Public Const QUOTE = """"

To use it:

strWhere = " Where sometextfield = " & QUOTE & Me.sometextstring & QUOTE & ";"

This will end up as:

Where sometextfield ="O'Tool"

This solves the problem of having a text field with an embedded apostrophe

You can do the same thing by use Chr() or """" and sometimes """ but the QUOTE is easy to remember and fairly obvious when reading the code.