By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,223 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,223 IT Pros & Developers. It's quick & easy.

Insert into failing if a surname has ' in it

P: 5
Hi all,

I have a chunk of sql in an Access module (Access 2003 btw) which runs perfectly until it hits a row where the surname has ' in it e.g. O'Brien. It fails on this. I've posted the code below, can anyone help me find a way to get it to work?

Expand|Select|Wrap|Line Numbers
  1. Set d = CurrentDb
  2. Set r = d.OpenRecordset("qryTriggersNotifyManagers2", dbOpenDynaset) 'open the recently created table
  3.  
  4. r.MoveFirst 'move to the first record
  5. Do Until r.EOF
  6.  
  7. strsql = "INSERT INTO tblAllTriggersForServiceBoardsArchive"
  8. strsql = strsql & " (Forename, Paynumber, title, Surname)"
  9. strsql = strsql & " VALUES  ( '" & r!Forename & "','" & r!PayNumber & "', '" & r!Title & "', '" & r!Surname & "') "
  10. CurrentDb.Execute strsql, dbFailOnError
  11.  
  12. r.MoveNext
  13. Loop
  14.  
  15. r.Close
  16. Set r = Nothing
Dec 20 '11 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,707
This is a very typical problem. You can handle it by doubling up any quote (') characters in your references, so line #9 should be :

Expand|Select|Wrap|Line Numbers
  1. strsql = strsql & " VALUES('" & Replace(r!Forename, "'", "''") & _
  2.                         "','" & r!PayNumber & "','" & r!Title & _
  3.                         "','" & Replace(r!Surname, "'", "''") & "')"
PS. If any other references can take a quote then they need to have the same treatment. If the data is pre-sanitised then numbers and title probably don't need it.
Dec 21 '11 #2

P: 5
works perfecly - thank you
Dec 21 '11 #3

NeoPa
Expert Mod 15k+
P: 31,707
Pleased to help Paul :-)
Dec 21 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.