0

my second question for my baptisme app.

I made a form with two comboboxes to search the name en first name of a child. The problem is when a ' character comes in the name I get an error. I think I understand why because in the sql-string the ' character is used. I found this method for searching on the internet, but my knowledge of SQL is below zero. So I don't know what to do about it. How can I do about this error??

Is there anyone who can help.

The error comes in the line: strSQL = "[Naam] = '" & Me.FComboNaam & "'". '(Naam = name) The error can also arise in the line: strSQL = strSQL & " [voornaam] = '" & Me.FComboVoornaam & "'" '(Voornaam = first name). So far I don't know if there are first names with a ' in it. I tried to resolve this problem with the instr function and delete the ' in the name, but this is not a solution because then the sql-string never find the name with the ' in it.

Here is my sub

Private Sub RequeryForm()

    Dim strSQL  As String
    
    If Len("" & Me.FComboNaam) > 0 Then
        strSQL = "[naam] = '" & Me.FComboNaam & "'"
    End If
    
    If Len("" & Me.FComboVoornaam) > 0 Then
        If Len(strSQL) > 0 Then
            strSQL = strSQL & " And "
        End If
        strSQL = strSQL & " [voornaam] = '" & Me.FComboVoornaam & "'"
    End If
    If Len(strSQL) = 0 Then 'als de filter leeg is deze afzetten
        Me.FilterOn = False
    Else
        Me.filter = strSQL 'als de filter niet leeg is deze aanzetten
        Me.FilterOn = True
    End If
    
End Sub

I search the internet and found the instr function but that doesn't help. I want to know if I can avoid this error one way or an other. The error I get is error 3075 during execution. syntax error. Operator is missing in query expression [Naam] = 'D'Hondt'

My SQL-string is not the problem, but the name with a ' in it: D'Hondt (=name) How can I find a name like D'Hondt

Gustav
  • 53,498
  • 7
  • 29
  • 55
tbennekik
  • 11
  • 2
  • You can use my [CSql function](https://stackoverflow.com/questions/43589800/syntax-error-in-insert-into-statement-whats-the-error/43591778#43591778). – Gustav Feb 06 '23 at 21:51
  • Does this answer your question? [MS Access 2010 VBA runtime error 3075 (searching with apostrophes)](https://stackoverflow.com/questions/25894454/ms-access-2010-vba-runtime-error-3075-searching-with-apostrophes) or https://stackoverflow.com/questions/16541682/a-better-way-to-add-apostrophes-with-sql-access-vba – June7 Feb 06 '23 at 21:55

0 Answers0