Syntax Error (Missing Opeator) in Query Expression

[JithuAccess]

Hello Guys,

I was trying to add the Values in a Combo Box on the fly. I have put the following code:



Private Sub Combo41_NotInList(NewData As String, Response As Integer)

Dim StrSQL As String
Dim i As Integer
Dim Msg As String

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Add New?")
If i = vbYes Then
StrSQL = "Insert Into [My Table] ([My Field]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute StrSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If



End Sub



It was working fine. But if I add a Text like "Children's Hospital" I am getting an error like below:

The problem is with the Apostrophe in the Word Childrens'. Could you kindly let me know how to resolve this. Or do I have to avoid the Apostrophe ?

Thanks a lot

 

[Ranman256]

use a query, it doesnt get syntax wrong.
you just run: docmd.openquery "qaInsertMyRec"

you used an apostropye in the name "children's" . you cant surround a quote with quotes.
you CAN use dbl quotes to surround single quotes.

but what if they enter a word with dbl-quote? You will need to replace them using REPLACE(word, oldChar, newChar)
THEN add the new word.

or just strip out all quotes before adding.

 

[Isaac]

Change this line

"values ('" & NewData & "');"

to

"values ('" & replace(NewData,"'","''") & "');"

...God I hate how AWF code tags adds a bunch of crap to my quotation marks and parenthesis.

Hope I got that right.

Your problem is the syntax single quote. Every programming language has a way to 'escape' syntactical stuff. In SQL, it's to "double up"

 

[JithuAccess]

Change this line

"values ('" & NewData & "');"

to

"values ('" & replace(NewData,"'","''") & "');"

...God I hate how AWF code tags adds a bunch of crap to my quotation marks and parenthesis.

Hope I got that right.

Your problem is the syntax single quote. Every programming language has a way to 'escape' syntactical stuff. In SQL, it's to "double up"

Million Thanks. It worked Perfectly

 

[Ranman256]

i have a global constant Q to rep dbl-quotes

public const Q = """"

then its
sSql ="insert..... values(" & Q & txtbox & Q & ")"

 

[JithuAccess]

use a query, it doesnt get syntax wrong.
you just run: docmd.openquery "qaInsertMyRec"

you used an apostropye in the name "children's" . you cant surround a quote with quotes.
you CAN use dbl quotes to surround single quotes.

but what if they enter a word with dbl-quote? You will need to replace them using REPLACE(word, oldChar, newChar)
THEN add the new word.

or just strip out all quotes before adding.

Thanks a lot for your time and Effort

 

[JithuAccess]

i have a global constant Q to rep dbl-quotes

public const Q = """"

then its
sSql ="insert..... values(" & Q & txtbox & Q & ")"

Thank You

 

[gemma-the-husky]

I actually use chr(34) rather than a string of " characters. I find it much easier.