I am having a problem when updating a field in a table through VBA SQL..the query works fine mostly, it only encounters a syntax error when the user enters the ' (apostrophe , or whatever it's called!)...
My code looks as follows:
Comments.SetFocus
If Comments.Text = "" Then
Comments1 = "NULL"
Else
Comments1 = "'" & Comments.Text & "'"
End If
DoCmd.RunSQL _
"UPDATE Bookings" & _
" SET [Comments] = " & Comments1 & _
" WHERE [BookingRef] = " & BookingRefx.Value & ";"
Is there a better way to do this? I am first transferring the data from the text box to another variable (Comments1), adding the apostrophes, then putting that to the SQL...i can see that the problem is because when the user enters an apostrophe, there will be more than the beginning and ending apostrophe, and thus raises SQL syntax error...
what's the better way to approach this?
My code looks as follows:
Comments.SetFocus
If Comments.Text = "" Then
Comments1 = "NULL"
Else
Comments1 = "'" & Comments.Text & "'"
End If
DoCmd.RunSQL _
"UPDATE Bookings" & _
" SET [Comments] = " & Comments1 & _
" WHERE [BookingRef] = " & BookingRefx.Value & ";"
Is there a better way to do this? I am first transferring the data from the text box to another variable (Comments1), adding the apostrophes, then putting that to the SQL...i can see that the problem is because when the user enters an apostrophe, there will be more than the beginning and ending apostrophe, and thus raises SQL syntax error...
what's the better way to approach this?