SQL Strings

[MrBelfry]

Hey there

I have an access database that sorts through some records. It uses a docmd.runsql thingy with a sql string constructed from the data in a list box so the code looks like this:

SqlStatement = "INSERT INTO mailing (id, name, title) VALUES ('" & lstID.ItemData(i) & "', '" & lstName.ItemData(i) & "', '" & lstTitle.ItemData(i) & "') "

Unfortunately if the itemdata contains an apostrophe it messes up the sql. Is there anyway to still use apostrophes in the list box that won't mess up my sql. The data in the list box is someones name so it is sometimes necessary to write O'Conner for example.

Thanks in advance

MrBelfry

 

[TonyJollans]

Hi MrBelfry,

If your itemdata can contain apostrophes it must either have the apostrophes doubled in the SQL, or be enclosed in double quotes. To make sure it is enclosed in double quotes in the generated SQL string you must put double double quotes in the VBA. You can continue to use single quotes for those strings which can never contain apostrophes but the following uses double quotes throughout.

SqlStatement = "INSERT INTO mailing (id, name, title) VALUES (

""

" & lstID.ItemData(i) & "

""

,

""

" & lstName.ItemData(i) & "

""

,

""

" & lstTitle.ItemData(i) & "

""

) "

Of course, if your itemdata could also contain double quotes then you need a more complex arrangement.

Enjoy,
Tony

 

[MrBelfry]

Thanks Tony

Things seem to be working very well ok now.

You is da bomb

MrBelfry