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