Filtering data for any character that begins with what the user input

[AceD]

Hello everyone I am trying to filter data in a table using vba where I have a split form setup. My goal is to filter data where the user can input a character such as "a" for the "FirstName" field and have results from the table filtered with persons first name that starts with "a". Here is my code so far one of the text boxes.



Private Sub txtFirstName_DblClick(Cancel As Integer)

If Me.Filter = "" Then
'Compares the values that begin with the input values in txtFirstName
'text box from the table field name FirstName
Me.Filter = FirstName & " LIKE '" & txtFirstName & "*'"
Else
'this line is suppose to append all the filtered data that were acquired
Me.Filter = Me.Filter & AND LastName & " LIKE '" & txtLastName & "*'"
End If
FilterOn = True

End Sub


I get a error in the else statement and please note that I am linking this form to an sql server so I can not delete or modify existing data in the table.

[ItsMe]

You need to get the AND operator within quotes. It is not a variable. You are using the literal text AND. Just like the operator LIKE is within quotes.

Me.Filter = Me.Filter & " AND " & LastName & " LIKE '" & txtLastName & "*'"

Also, LastName should not be a variable. THis should be a field name in the table/query. so....


The stuff in red is the literal text you want included in your filter property.

Me.Filter = Me.Filter & " AND [LastName] LIKE '" & txtLastName & "*'"