Possible quotation marks in an VBA-built SQL statement

[PaultheS]

Right now, I have a form that users can fill out and send e-mails from. It's supposed to save the body, subject, date, etc. when every e-mail is sent. I've been doing this with an SQL statement that is built in VBA.

So, something like:

"INSERT INTO [tbl: History] ([Body], [Subject], [Date]) VALUES ('" & body & "', " & subject & ", #" & Date & "#)"

where body and subject are local variables.

It works fine, but the problem is when a user puts single or double quotes in the body or subject. It gets interpreted in weird ways and the statement breaks.

Is there a way I can surround body and subject so it really knows what they are (I was thinking like a triple or quadruple quote, if they existed)?

If not, is there a good way to do this?

 

[fredericofonseca]

why, why don't people search the forums before posting.

see faq222-2244

and faq709-1526 for your particular question.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Remou]

Hi
This is not an answer, but an alternative:
Single-Quotes are causing problems! faq709-1526

 

[PaultheS]

Thanks. I did notice these but thought there might be an easier way. The security is not an issue, as the only guy using it is running the database anyway...

But, if there's no way to tell Access that this upcoming string can have whatever characters, then I guess it'll have to do. Thanks for your help.

 

[Remou]

Hi
I copied (stole) this from some place but cannot remember where:
[tt]sText = "Commedia Dell'Arte"
sText = Replace(sText, "'", "''")[/tt]
I have tried SQL with double quotes, and it seemed ok, because of the single containing quotes: 'Hello"', '"Hello"'