I have a filter on a continuous form that uses a Combo Box to select records to match; the code is:

Private Sub SelectHospitalCbo_AfterUpdate()

   Me.Filter = "[ContactHospital] = " & "'" & Me.SelectHospitalCbo & "'"
   Me.FilterOn = True
   
End Sub

This was working fine until I discovered that if the ContactHospital field includes an apostrophe (e.g. Children's Hospital) I get an error message:

Run-time error '3075': Syntax error (missing operator) in query expression '[ContactHospital] = 'Children's Hospital".

I understand why the error is occurring, but I can't find a workaround. A recent question on this forum seemed to have a similar problem to mine, but there were no answers. Does this mean I can't get around it?

In case anyone wants to suggest removing all the apostrophes form the hospital names, I would consider that, but unfortunately this database interacts with a (much larger) database where the hospital names can't be changed and have to match, so that's not an option for me.

Any help from more experiences Access developers appreciated!

CodePudding user response：

Options:

1. filter by numeric hospital ID instead of its name

2. "[ContactHospital] = '" & Replace(Me.SelectHospitalCbo, "'", "''") & "'"

3. "[ContactHospital] = """ & Me.SelectHospitalCbo & """"

4. "[ContactHospital] = " & Chr(34) & Me.SelectHospitalCbo & Chr(34)