VBA SQL syntax error (1 Viewer)

ray147

Registered User.
Local time
Today, 20:01
Joined
Dec 13, 2005
Messages
129
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?
 

allan57

Allan
Local time
Today, 20:01
Joined
Nov 29, 2004
Messages
336
The following should get you round the problem:-

Dim strQuote As String
strQuote = """"

Comments.SetFocus
If Comments.Text = "" Then
Comments1 = "NULL"
Else
Comments1 = Comments.Text
End If

DoCmd.RunSQL _
"UPDATE Bookings" & _
" SET [Comments] = " & strQuote & Comments1 & strQuote & _
" WHERE [BookingRef] = " & BookingRefx.Value & ";"
 

ray147

Registered User.
Local time
Today, 20:01
Joined
Dec 13, 2005
Messages
129
allan,
thanks for your answer..

it's working fine with apostrophes...
however a syntax error is raised if the user attempt to enter an inverted comma (")..

:(

is there a way how to get round both problems?

thanks
 

ray147

Registered User.
Local time
Today, 20:01
Joined
Dec 13, 2005
Messages
129
i really need to find a solution aroud this..can't figure something out...

i have this textbox and i want to allow the user to enter any type of character in..including apostrophe and inverted commas...is this at all possible? i'm getting syntax errors now due to these 'strange' character input..


thanks in advance
 

pono1

Registered User.
Local time
Today, 13:01
Joined
Jun 23, 2002
Messages
1,186
The replace function is helpful when it comes to the problem you're describing.

Say you have a table named TblNames and it has a text field named Last_Name. Also, say you have a textbox on a form named TxtLastName. And, lastly, say you have a command button to insert a new row into your table, filling the last_name field...

The command button's click event handler's code...

Code:
Private Sub CommandButton_Click()

'Call a function to scrub the string.
   Dim MyName As String
   MyName = FixMyString(TxtLastName)
    
'Create SQL string.
   Dim s As String
   s = _
   "INSERT INTO TblNames " & _
   "(Last_Name) " & _
   "VALUES(" & "'" & MyName & "'" & ")"

'Add record using the SQL string.
   CurrentDb.Execute s

End Sub

Function called...

Code:
Function FixMyString(Text As String) As String
' Call this function to scrub strings inserted into a table using SQL.

   Dim MyString As String
   MyString = Replace(Text, "'", "''")
   MyString = Replace(MyString, """", """")
   FixMyString = MyString

End Function

More on the Replace Function.

Regards,
Tim
 

ray147

Registered User.
Local time
Today, 20:01
Joined
Dec 13, 2005
Messages
129
Thanks Tim,

That's great...problem solved!

Ray :)
 

Users who are viewing this thread

Top Bottom