Avatar of gtmathewDallas
gtmathewDallas
 asked on

Syntax error (missing operator) when using single and double apostrophe(' and ") with the input data

Dear Experts, Getting Syntax error (missing operator) when inserting data with single and double apostrophe(' and ") .
if i use this way (""" & Me.Text_box & """) it will take single apostrophe but cannot resolve when double apostrophe came in.
Please help
Thanks
Microsoft AccessSQL

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Hamed Nasr

Try:
Chr(34) & Me.Text_box & Chr(34)
gtmathewDallas

ASKER
I tried but showing run time error 2471 :(
gtmathewDallas

ASKER
DCount("*", "REVIEW_TYPE", "Review_Name = Chr(34) & Me.Text_RVTEdit.value & Chr(34) and Review_Type_Id <>  CLng('" & Me.Combo_RVEdit & "')")
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
chopper_irl

it looks like you need to escape the characters, which is done by a single quote so
('I'll be there in a second O'Connor') will be ('I''ll be there in a second O''Connor')
or
(Quote:"Show me the money") would be (Quote':'"Show me the money'")

this is not the safest/best way to escape characters but its a start...
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

How about

Chr(34) & Replace(Replace(Replace(Me.Text_box, Chr(39),""),Chr(39) & Chr(39),""), Chr(34),"")   & Chr(34)
gtmathewDallas

ASKER
Actually the user can be enter any type of key values (', ",&,$,% etc ) to the database, is there any way to allow this?
I was using the controls this way-  '" & Me.Text_Box & "' but its not working..
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Hamed Nasr

Try:

DCOUNT ("*", "REVIEW_TYPE", "Review_Name = "  & Chr(34) & Me.Text_RVTEdit.value & Chr(34)  & " and Review_Type_Id <>  CLng(" & Me.Combo_RVEdit & ")")
Jeffrey Coachman

<Getting Syntax error (missing operator) when inserting data with single and double apostrophe(' and ") .
Not sure how you are "Inserting", but this works fine for me:

CurrentDb.Execute "INSERT INTO YourTable (YourTextField) Values(" & "'" & Replace(Me.txtTextWithApostrophe, "'", "''") & "'" & ")", dbFailOnError
test.mdb
Jeffrey Coachman

There may be a way to "tighten up" the syntax in my above post, but again, it works fine for me...
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
mbizup

Any of the above posts should work, but here's another approach using the Parameters collection.  This Access VBA is similar to what you would use in .Net:

Private Sub cmdGetCount_Click()
    Dim qd As New DAO.QueryDef
    On Error GoTo EH
    CurrentDb.QueryDefs.Delete ("qtemp")
    Set qd = CurrentDb.CreateQueryDef("qtemp", "SELECT COUNT(*) AS TotReviews FROM REVIEW_TYPE WHERE Review_Name = @ReviewName AND Review_Type_Id <> @ID ")
    With qd
        .Parameters("@ReviewName") = Me.Text_RVTEdit
        .Parameters("@ID") = Me.Combo_RVEdit
    End With
    MsgBox qd.OpenRecordset.Fields("TotReviews")
    Set qd = Nothing
    Exit Sub
EH:
    If Err.Number = 3265 Then Resume Next
    MsgBox "ERROR " & Err.Number & ": " & Err.Description
   
End Sub

Open in new window


With the parameters collection, you don't need to worry about escaping special characters (because you're not using text or date delimiters), and if you ever upsize to a SQL platform, this method avoids SQL Injection problems in your action queries.
Jeffrey Coachman

In Reviewing this post, I discovered I was looking at this from the wrong point of view
(SQL Insert)

So my syntax is valid, but not really specific for your situation (DCount)
:-(

Jeff
gtmathewDallas

ASKER
Thanks Fyed, Its really simple and a good solution, Thanks.

I want to thank you to all other experts who helped me in this issue...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

Glad to help.

I have another version of that function which allows you to identify the wrapper, so can choose a single quote (used with SQL Server pass through queries), double quote, or even # for wrapping numbers.