Solved

Access: how to escape single quotes

Posted on 2009-06-29
6
236 Views
Last Modified: 2013-11-27
Hi x-perts

Here is a simple SQL string:

It obviously returns an error, when a text string contains a quotation mark itself (like dog's)

How can I fox this?

Thanks
SQL = "INSERT INTO Betas (" & strFields & ") VALUES (" & strBetas & ")"
 
strFields = "[field1],[field2]"
 
strBetas = firstNumber & "'" & textValue & "'"
 
textValue = "dog's"

Open in new window

0
Comment
Question by:andy7789
[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
  • 3
  • 2
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 150 total points
ID: 24742089
replace the single quote with 2 singlequotes
textValue = "dog''s"  
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24742107
Aneesh is correct, but you just in case you didn't already notice, you will need to have a comma in strBetas.
SQL = "INSERT INTO Betas (" & strFields & ") VALUES (" & strBetas & ")"
 
strFields = "[field1],[field2]"
 
strBetas = firstNumber & ",'" & textValue & "'"
 
textValue = "dog''s"

Open in new window

0
 

Author Comment

by:andy7789
ID: 24742160
thank you, but it doesn't work

1) coma is just a typo here (not in my actual code)
2) i cannot modify the dog's string, because it is taken as a field value from another table

is there a function to escape quotes like in php?
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 350 total points
ID: 24742183

strBetas = firstNumber & ",'" & replace(textValue, "'", "''") & "'"

Open in new window

0
 

Author Comment

by:andy7789
ID: 24742283
works great - thank you!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24742288
You are welcome!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question