Help with VBA sql string 1

[tigersden]

Hi,
I have written a function to loop through a table & concatenate all the Add_Info for a specific job & write it back to new table in 1 record.
On some the existing data I am getting errors, these seem to be caused by the use of ' in the concatenated text, also it appears that other characters may also be causing this.
Please could you look at my sql statement below as I believe I may have used the wrong " or ' in it.
strSQL = "INSERT INTO tblJobInfo (JOBL, ADD_INFO) " _
& "VALUES ( ' " & strJobNo & " ' , ' " & strInfo & " ' )"

JOBL is a unique job number which is alphanumeric & ADD_INFO is also alphanumeric.

Thanks in advance
Tim

 

[gmmastros]

When you want an apostrophe to store in the database, you need to double it.

Ex.

Insert Into tblJobInfo(JOBL, ADD_INFO) Values('abc123', 'Jack O''Neil')

So... to make your query work, you can do this....

strSQL = "INSERT INTO tblJobInfo (JOBL, ADD_INFO) " _
& "VALUES ( ' " & Replace(strJobNo, "'", "''") & " ' , ' " & Replace(strInfo, "'", "''") & " ' )"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tigersden]

Hi George,
Thank you so much.
I have been trying all sorts of combinations out for hours & finally decided to post it on here.
Thanks once again
Tim

 

[gmmastros]

glad to help.

If you get better with google searches, this wouldn't have been a problem. For example, if you google search on "microsoft access apostrophe problem" you get about 147,000 hits.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom