• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • Last Modified:

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
0
GPSPOW
Asked:
GPSPOW
  • 3
  • 2
1 Solution
 
Gustav BrockCIOCommented:
This should do:

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

or:

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

/gustav
0
 
GPSPOWAuthor Commented:
Gustav,

I do not see the difference between the two statements.

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

Glen
0
 
Rey Obrero (Capricorn1)Commented:
try this

    Me.Form.Filter = "[Company] =" & chr(34) & Me.Combo273 & chr(34)
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Gustav BrockCIOCommented:
Look again - or copy-paste - it's the quotes.

or use a variable so you can debug:

    Dim CompanyName As String
    CompanyName = Replace(Me!Combo273.Text, "'", "''")
    Me.Filter = "[Company] = '" & CompanyName & "'"

/gustav
0
 
GPSPOWAuthor Commented:
I copied the statement and it worked.

Thanks

Glen
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now