1

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!

HelenC
  • 13
  • 3
  • 1
    Just in case, you can avoid this problem entirely and it is probably better practice to follow custom. it is custom for comboboxe's to have 2 columns. The id, and the description. the id is the bound column and is invisible because it is set to 0 width. Then replace all instances Here: SelectHospitalcbo in the header and ContactHospital in the detail section with the 2 column combo boxes. When you need the string value for reports and such you just replace the id at that time. – mazoula Jan 23 '23 at 06:02
  • I've previously used that method, but it caused other problems! I might have to do a rethink if I can't overcome this issue. Thanks. – HelenC Jan 23 '23 at 06:56

1 Answers1

0

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)

June7
  • 19,874
  • 8
  • 24
  • 34
  • To answer the question asked. if you are looking for a quik-fix then you can handle this particular problem by calling Replace and then delimiting the ' properly. For instance wrap "[Contact... with: Public Function DelimitApostrophes(stringwithapostrophe As String) As String DelimitApostrophes = Replace(stringwithapostrophe, "'", "'") End Function End Function or just wrap with the replace part. – mazoula Jan 23 '23 at 06:33
  • yes, that really is "'" with "'" but in context it means replace any apostrophes (') with a quoted apostrophe (" ' ". see replace – mazoula Jan 23 '23 at 06:36
  • @mazoula, code in your comments show replacing apostrophe with apostrophe - no change. – June7 Jan 23 '23 at 07:13
  • @june7, thanks so much for your response, option 2 was the one that worked! – HelenC Jan 24 '23 at 01:11