Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

asked on

MS Access VBa Syntax error for filtering

I have copied the following VBA code to filter an Access list.  I am getting a syntax error for the

Private Sub Combo273_Change()

  ' If the combo box is cleared, clear the form filter.
  If Nz(Me.Combo273.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
    
  ' If a combo box item is selected, filter for an exact match.
  ' Use the ListIndex property to check if the value is an item in the list.
  ElseIf Me.Combo273.ListIndex <> -1 Then
    Me.Form.Filter = "[Company] = '" & _
                     Replace(Me.Combo273.Text, "'", """) & "'"
    Me.FilterOn = True
    
  ' If a partial value is typed, filter for a partial company name match.
  Else
    Me.Form.Filter = "[Company] Like '*" & _
                     Replace(Me.Combo273.Text, "'", """) & "*'”
    Me.FilterOn = True

  End If
  
  ' Move the cursor to the end of the combo box.
  Me.Combo273.SetFocus
  Me.Combo273.SelStart = Len(Me.Combo273.Text)
End Sub

Open in new window


I get a Compile  and Syntax error for the following:

Me.Form.Filter = "[Company] = '" & _
                     Replace(Me.Combo273.Text, "'", """) & "'"

Thanks

Glen
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

This should do:

Me.Filter = "[Company] = '" & _
                      Replace(Me!Combo273.Text, "'", """) & "'"

or:

Me.Filter = "[Company] = '" & _
                      Replace(Me!Combo273.Text, "'", "''") & "'"

/gustav
Avatar of GPSPOW

ASKER

Gustav,

I do not see the difference between the two statements.

I tried both and they are still giving me an error.

Glen
try this

    Me.Form.Filter = "[Company] =" & chr(34) & Me.Combo273 & chr(34)
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

I copied the statement and it worked.

Thanks

Glen
You are welcome!

/gustav