Access2000 Escape Characters

[kilando]

Hello Everyone,

I'm new to using Access and Visual Basic and am learning as I go along on this project for work.

I'm stumped trying to figure out what works as an escape character in VBA.

Basicaly I want to convert this SQL statement:

WHERE (((Record.Object) Like "*" & [Enter Object] & "*&quot)

into a string so I can use it as the wherecondition argument of the OpenForm method.

My problem is with the quotes around the asterix in the SQL statement. my string for the where condition (called stLinkCondition) is like this:

stLinkCondition = "[Record.Object] = Like "*" '" & Me![ObjectEntered] & "' "*" "

(ObjectEntered is a name of a text filed in a form)

This doesn't work because of the " from the SQL, can anyone tell me what I should do to get the " into the string or something that will achieve the same goal?


Thanks

 

[psemianonymous]

There are a bunch of workarounds:

'1.  Use "" to represent one double-quote
msgbox "The following word ""word"" is surrounded in quotes"


'2.  Use a single-quote ' symbol instead
msgbox "The 'single-quote' works identically in SQL, and sometimes it is the ONLY set of quotes recognized by other SQL-based products"

'3.  Use Chr(34), it's foolproof
strHasQuotes = "Trying to store data with ""quote marks"" in the data itself, this can mess up SQL statements"
msgbox "UPDATE TBL SET FLDMEMO = " & Chr(34) & strHasQuotes & Chr(34) & "WHERE ID = 1"

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[kilando]

Thanks for the help.