The Apostrophy Problem?

[HarryW]

Yes, because, in a similar way to Pascal, two single quotes in a row is interpreted as one single quote contained within a string.

I just thought maybe that could do with explaining.

[Nator]

That worked. Thanks for the help.

[Nator]

Does anyone have a work around for handling strings containing apostrophies that are being passed into a SQL statement?
Example. I have form with a textbox for Last Name, First Name and Phone Number. If the lastname box contains something like O'Hara I get a syntax error when I try to run
"INSERT INTO contacts (lastname, firstname, phone) VALUES ('" & txtLastname.text & "', '" & txtFirstname.text & "', '" & txtPhone.text & "'). The apostrophy in O'Hara is being confused with the tick in my SQL statement. I am using an Access database.
Any help would be appreciated.

[Clunietp]

you can use the VB6 REPLACE function to find those single quotes and convert them to double single quotes...

dim strSQL as string

strSQL = "Select * from Customers where CustomerID = '" & replace(text1.text, "'", "''") & "'"

HTH

Tom