SQL Update on data with an apostrophe in it

[Deb Johnson]

I am trying to update an existing database record. The value I am trying to put in the [Title] field of the database has an apostrophe in it. I cannot get the syntax of my SQL update query coded right so the value updates the way it should.

This is the SQL

strSQL = "UPDATE Video " & _
"SET [Title] = '" & txtTitle.Text & "', " & _
"[Category] = '" & cboCategory.Text & "', " & _
"[Studio ID] = '" & cboStudioID.Text & "', " & _
"[Length] = " & intlength & _
" WHERE [Movie Number] = '" & mskMovieNumber.Text & "'"

For example when txtTitle.Text is equal to My Mother's Diary, I get a syntax error.

I tried to change the SQL for the title part to:
"SET [Title] = "" & txtTitle.Text & "", " & _
which updated the Title on my record to & txtTitle.Text &

I also tried this:
"SET [Title] = ''" & txtTitle.Text & "'', " & _
which gave my another syntax error.

I am using VB 6.0 and a Microsoft Access 97 database file.

Any help you can give me would be most appreciated!

Deb

[PJB]

here's a function i've used in .ASP files before but have never tried it in VB:

Replace(txtTitle.Text, "'", "''")

I usually set a variable = to the above statement then put the variable in my SQL statement(don't know if that makes any difference), if that doesn't work look for something along those lines, i don't think the answer is changing the SQL statement.