Another string in sql question- single and double quotes in string 2

[drkhelmt]

Hi all-

We're back with another installment of the single quote/double quote problem. I have been reading posts for a while now on this, but none quite fit the problem...

sqlst = "INSERT INTO saleItem (saleID,quantity,siDesc,markup,statusID,colorID,width,height,styleID,typeID,rails,picketPerFoot,guage) 
VALUES (" & sID & "," & rst!quantity & "," & Chr(34) & rst!siDesc & Chr(34) & "," & rst!markup & ",1," & rst!colorID & "," & rst!width & "," & rst!height & "," & rst!styleID & "," & rst!typeID & "," & rst!rails & "," & rst!picketPerFoot & "," & rst!guage & ");"

All is well, until rst!siDesc evaluates to something like:
3"SQ X 9' X 14GA POST
or
6' X 8'W LFGRD PANEL
or
1 1/2"SQ PRESS STEEL CAP

I have thought about replace() or delaring a constant "DQ" as one poster mentioned in another thread, but I don't think those will work since my string can contain both single and/or double quotes in the same string.

Any help is appreciated.

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[PHV]

You may try this:
& rst!quantity & [tt]",'" & Replace(rst!siDesc, "'", "''") & "',"[/tt] & rst!markup

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[drkhelmt]

PHV, you're a flipping genius!

But why??? The code works and seems to work for all variations of the string, but what I don't understand is why replacing single quotes (') with ('') allows them to evaluate as ('') then when the docmd.runsql (sqlst) is run, the double single quotes ('') are put into the table as (')? Would you be so good as to explain this one to me?

Andrew
a.k.a. Dark Helmet

"What's the matter Colonel Sandurz? Chicken?

 

[PHV]

This is the SQL syntax for embedding a single quote in a single quoted literal string.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886