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