Help with text string

[Acropolis]

Hi Guys,

Have a feeling there's a really simple solution to this, but I'm struggling to find it at the moment.

Have the code below, inserting the entry in a text box into a table, the below is just a test, works absolutely perfectly, unless there is a ' in the text such as "it's" then it all goes to pot and it errors.

I have tried CStr but it didn't make any difference, is there anything else I can try?

Thanks

 

[namliam]

Have the code below

What code?

You probably have something like:

" bla bla, '" & YourVariable & "', bla bla"

Which becomes

" bla bla, 'it's', bla bla"

Which breaks because of the 3 ' that are in there... the way around this is to
1) Escape the ' in the strings
Ignore this
2) not use ' to denominate string/text values, try using double quotes (") instead, which in turn will not allow text like it"s, but that is EXTREEMLY rare

" bla bla, """ & YourVariable & """, bla bla"

Yes, that is two times 3 " in sequence, that is meant to be that way.

 

[Acropolis]

I suppose it does help if I post the code that I mention lol, been a long day digging a big hole in the garden! But you were spot on with what you guessed as my code and also with the solution.

Many thanks, all working wonderfully now.

 

[TJPoorman]

You could also do

" bla bla, '" & Replace(YourVariable, "'", "''" & "', bla bla"

This would turn your string to

" bla bla, 'it''s', bla bla"

Since it sees 2 ' it changes it to one in the SQL string.

Personally I find this easier than trying to remember how many "s I have to put in.

 

[CJ_London]

use the replace function to replace a single quote with two single quotes
replace(myStr,"'","''")