ms-accessruntime-errorapostrophe

Access: Filtering form on field - fails when fields contains an apostrophe

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!

Solution

  • 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)