Replace character in VBA Excel code 1

[UnsolvedCoding]

I want to replace all ' in the string with nothing so that when I load it into SQL I don't keep running into problems. The code given works to replace only one ' and then stops looking.

I have been looking for awhile and can't find any code samples to say how to do this and I know it can be done.

My code -
sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "")

How do I replace all ' in the string?

 

[Bong]

do while instr(1,sCOMMENT_TEXT,"'")>0
    sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "")
loop

_________________
Bob Rashkin

 

[UnsolvedCoding]

Worked like a charm.

Thanks.

 

[Andrzejek]

Your code should work like you had it in the first place, replacing all single quotes:

sCOMMENT_TEXT = "This is ' some ' text with '' some ' quotes"

sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "")

Debug.Print sCOMMENT_TEXT[blue]
This is  some  text with  some  quotes[/blue]

So I don't know what you mean by: "The code given works to replace only one ' and then stops looking"

Have fun.

---- Andy

 

[MarkWalsh]

The proper way to deal with single quotes in SQL is to replace each single quote with 2 single quotes

e.g.

sCOMMENT_TEXT = Replace(sCOMMENT_TEXT, "'", "''")

This way, if you enter "Bob O'Brien" into a record, you won't end up with "Bob OBrien".