Does not equal filtering using vba

[mr879]

Hello everyone I have been trying to filter data for the "First Match" and "Second Match" that does not equal certain values so the best way I thought of doing this was to create an array of possible values for the field that you would want to filter out and then use a for loop to go through each possible value and filter the data accordingly and at the same time append the values so the previous filtered data will not be lost.

First Name	Last Name	First Match	Second Match
James	Matheson	0	2
Monroe	Labonson	4	3
Barack	Obama	2	5
Frederick	Douglas	3	4
Steve	MCGowan	1	1
John	Seals	15	15
Mike	Omalley	14	15

Private Sub notEqual_Click()

Dim strArray(0 To 13) As String

strArray(0) = "0"
strArray(1) = "2"
strArray(2) = "3"
strArray(3) = "4"
strArray(4) = "5"
strArray(5) = "6"
strArray(6) = "7"
strArray(7) = "8"
strArray(8) = "9"
strArray(9) = "10"
strArray(10) = "11"
strArray(11) = "12"
strArray(12) = "13"
strArray(13) = "14"

For i = LBound(strArray) To UBound(strArray)

'txtF and txtL are text box values of "First Match" and "Second Match"
If txtF <> "15" And txtF <> "1" And txtL_
<> "15" And txtL <> "1" Then
Me.Filter = "First Match = """ & strArray(i) & """"
Me.Filter = Me.Filter & " AND Second Match = """ & strArray(i) & """"
End If
Next
FilterOn = True

End Sub

I get an error that says syntax error (missing operator) in query expression 'First Match0". Am I possibly appending the values the wrong way? I feel like there is something wrong with the code inside the for loop.

[lfpm062010]

Do you know where you error is occuring? Did you do a step by step debug?

I can see two places might be a problem.

1) The "txtF" and "txtL" are text box. You will need "Me.txtF" and "Me.txtL" to reference them.
2) THe "First Match" and "Second Match". What are they? If they are columns on the table, then you will need "[" and "]" around the "First Match" and "Second Match". Like "[First Match]" and "[Second Match]" (without the quotes).

There might be other error. That why if you know which line is breaking. It will help trememdously.

[mr879]

Thank you for your quick reply!!

The error is shown in this line:

Me.Filter = "First Match = """ & strArray(i) & """"

[mr879]

I have made the suggestions you have presented and now when i Debug it says data type mis match expression and highlights the line FilterOn=True

is it possible that I am implying the filter value is a string instead of a numeric type?

[lfpm062010]

Try

Me.FilterOn = True

[mr879]

I have already tried that I still get the same error.
I have added a portion of the data table I am using in my first post it might help.

[lfpm062010]

Is the form build on a table/query?

Is [First Match] and [Second Match] a field/column on the table/query?

How is [First Match] and [Second Match] defined, number, text, Yes/No, ... etc?

If it is text, then quotes are required.

If it is number, then quotes are not allowed.

[mr879]

The form is built in which the data table is accessed through SQL server.

First Match and Second Match are columns in the table.

They seem to be defined as numbers.

First Name	Last Name	First Match	Second Match
James	Matheson	0	2
Monroe	Labonson	4	3
Barack	Obama	2	5
Frederick	Douglas	3	4
Steve	MCGowan	1	1
John	Seals	15	15
Mike	Omalley	14	15