How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.
Course Of The Month
6 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Syntax error (missing operator) when using single and double apostrophe(' and ") with the input data
Posted on 2013-01-02
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
if i use this way (""" & Me.Text_box & """) it will take single apostrophe but cannot resolve when double apostrophe came in.
Please help
Thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
2- +4
14 Comments
DCount("*", "REVIEW_TYPE", "Review_Name = Chr(34) & Me.Text_RVTEdit.value & Chr(34) and Review_Type_Id <> CLng('" & Me.Combo_RVEdit & "')")
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...
('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...
Active 1 day ago
How about
Chr(34) & Replace(Replace(Replace(Me
Chr(34) & Replace(Replace(Replace(Me
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..
I was using the controls this way- '" & Me.Text_Box & "' but its not working..
Active today
Try:
DCOUNT ("*", "REVIEW_TYPE", "Review_Name = " & Chr(34) & Me.Text_RVTEdit.value & Chr(34) & " and Review_Type_Id <> CLng(" & Me.Combo_RVEdit & ")")
DCOUNT ("*", "REVIEW_TYPE", "Review_Name = " & Chr(34) & Me.Text_RVTEdit.value & Chr(34) & " and Review_Type_Id <> CLng(" & Me.Combo_RVEdit & ")")
Active 4 days ago
<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.txtTextWithApos
test.mdb
Not sure how you are "Inserting", but this works fine for me:
CurrentDb.Execute "INSERT INTO YourTable (YourTextField) Values(" & "'" & Replace(Me.txtTextWithApos
test.mdb
Active 4 days ago
There may be a way to "tighten up" the syntax in my above post, but again, it works fine for me...
Active today
I use a function when to format my text strings that are either SQL statements or criteria strings. The function looks like:
You use the function like:
DCount("*", "REVIEW_TYPE", "Review_Name = " & Quotes(Me.Text_RVTEdit) & " AND [Review_Type_Id] <> CLng(" & Me.Combo_RVEdit & ")")
You really don't need to wrap the value from Me.Combo_RVEdit in single quotes, like you had, prior to using the clng() conversion function.
Public Function Quotes(varTextToQuote As Variant) As String
Quotes = Chr$(34) & Replace(Nz(varTextToQuote, ""), Chr$(34), Chr$(39)) & Chr$(34)
End Function
You use the function like:
DCount("*", "REVIEW_TYPE", "Review_Name = " & Quotes(Me.Text_RVTEdit) & " AND [Review_Type_Id] <> CLng(" & Me.Combo_RVEdit & ")")
You really don't need to wrap the value from Me.Combo_RVEdit in single quotes, like you had, prior to using the clng() conversion function.
Active 1 day ago
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:
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.
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
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.
Active 4 days ago
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
(SQL Insert)
So my syntax is valid, but not really specific for your situation (DCount)
:-(
Jeff
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...
I want to thank you to all other experts who helped me in this issue...
Featured Post
Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s
ManageEngine
webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses
Course of the Month6 days, 19 hours left to enroll
726 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.