Insert into failing if a surname has ' in it

[Paul Doree]

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?

Set d = CurrentDb
Set r = d.OpenRecordset("qryTriggersNotifyManagers2", dbOpenDynaset) 'open the recently created table

r.MoveFirst 'move to the first record
Do Until r.EOF

strsql = "INSERT INTO tblAllTriggersForServiceBoardsArchive"
strsql = strsql & " (Forename, Paynumber, title, Surname)"
strsql = strsql & " VALUES  ( '" & r!Forename & "','" & r!PayNumber & "', '" & r!Title & "', '" & r!Surname & "') "
CurrentDb.Execute strsql, dbFailOnError

r.MoveNext
Loop

r.Close
Set r = Nothing

[NeoPa]

This is a very typical problem. You can handle it by doubling up any quote (') characters in your references, so line #9 should be :

strsql = strsql & " VALUES('" & Replace(r!Forename, "'", "''") & _
                        "','" & r!PayNumber & "','" & r!Title & _
                        "','" & 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.

[Paul Doree]

works perfecly - thank you

[NeoPa]

Pleased to help Paul :-)