How do I deal with single apostrophes

[PeterG]

I have inherited some code that has worked well until an Irish member joined us.

His name "O'Riordan" has caused problems

Part of our basic code looks like this

Public Sub Check_name()
 
fieldmanager = Name
manager = "O'Riordan Patrick"
filterReport = "qry_staff"
 
Set rstManager = DBEngine(0)(0).OpenRecordset("SELECT distinct name FROM " _
                & filterReport & " where name = " & "'" & manager & "'")
 
  records = rstManager.RecordCount
  MsgBox ("No of records is " & records)
  rstManager.MoveFirst
  manager_name = rstManager.Fields("name")
 
  MsgBox ("Name is " & manager_name)
 
 
End Sub

but the query fails because his name has the single apostrophe.

Can anyone advise how I correct this programmatically as there are likely to be others in the corporate staff table.

[NeoPa]

You should find the code in SQL Injection Attack to be helpful here.

Further info can be found at Quotes (') and Double-Quotes (") - Where and When to use them.

[NeoPa]

Sorry.

I just reread the first article and the code I expected (which simply doubles up all instances of these characters) doesn't appear to be there.

Your solution is as simple as that though. Does that help?

[missinglinq]

Also you appear to have a field called Name! Name is a Reserved Word in Access, and sooner or later this is going jump up and bite you in the rump! You really need to change it to something else.

Welcome to Bytes!

Linq ;0)>

[PeterG]

Thanks for the references. It's pointing me in the right direction so I'll spend today playing with it.

As a newbie to Access vba it is helpful having this resource

[NeoPa]

We're always happy to help those who are willing to learn :)

Welcome to Bytes!