Filter based on multi-column listbox looks only at first criteria (1 Viewer)

[Margarita]

Hello,
I am trying to filter a form based on a listbox selection made by the user:

Me.Filter = "[FiscalYear] = " & Me.listbox1.Column(0) & " And [Num1] = " & Me.listbox1.Column(1) & " and [Num2]= " & me.listbox.column(2)

This works for me ONLY if I have one column in the listbox by which I wish to filter. If I try to filter by more than one field as above, it fails. My listbox has the columns FiscalYear, Num1, Num2. When I specify a filter criteria for my detail section using all the three fields I wish to filter, here is what happens:

-If I select a record from the listbox with a fiscalyear that is different from the fiscal year of the current record, then it jumps to the FIRST record with the fiscal year specified by my listbox selection, which is NOT necessarily the record with the right Num1 and Num2- it just jumps to the first record with the relevant fiscal year.
-If I select a record from the listbox with a fiscalyear that is the same as the fiscal year of the current record that I am on, then it doesn't filter at all- just stays as it is.

Can anyone spot why my piece of code is filtering only on the first part of the criteria and ignoring the other two? This code is sitting in the on-click event of the filter button in the header of the form.

Thank you very much.

 

[Pat Hartman]

Are the columns numeric or text? If text, they need to be enclosed in quotes.

Me.Filter = "[FiscalYear] = '" & Me.listbox1.Column(0) & "' And [Num1] = '" & Me.listbox1.Column(1) & "' and [Num2]= '" & me.listbox.column(2)  & "'"

 

[Margarita]

Are the columns numeric or text? If text, they need to be enclosed in quotes.

Me.Filter = "[FiscalYear] = '" & Me.listbox1.Column(0) & "' And [Num1] = '" & Me.listbox1.Column(1) & "' and [Num2]= '" & me.listbox.column(2)  & "'"

Hello, yes, they are text and in my actual code they are enclosed in quotes- I just typed the code quickly in this post.
THank you.

 

[Pat Hartman]

Are you turning the filter on after you set it? It is always better to copy and paste the actual code. Then we don't have to waste our time with typos that aren't in the actual code.

 

[Margarita]

Hello, this is the actual code (the whole thing- there is nothing else in this click event):

Me.FilterOn = False

Me.Filter = "[FiscalYear] like '" & Me.ToFilter.Column(0) & "' And [Num1] like '" & Me.ToFilter.Column(1) & _
"' and [Num2] like '" & Me.ToFilter.Column(2) & "'"
 
Me.FilterOn = True

I first did it with = instead of like. That did the same thing. No change with switching to 'like'.

I put the filteron= false at the beginning in hopes that what was causing my problem is that the filter can't re-filter after one filter criteria is already set. But, of course, that was just placebo for my fragile access-ruined nerves. It did nothing. I do not know what would happen if I switch the order of the criteria- maybe if I put either Num1 or Num2 first, it will start filtering by that field and start ignoring FiscalYear, or it might start working.. I don't know. I will give that a shot when I am back in the office tomorrow. In the meantime, I am basically treading water in one spot and not going anywhere with this thing. I will post back if I realize what is causing me all this grief.
Thanks for looking at this!

 

[Pat Hartman]

Go back to =. You would ONLY use LIKE in conjunction with wild card characters. Never use it when you have complete values since it can force the database engine to do a full table scan rather than use indexes to find the records you are seeking.

The filter property can be tricky. Print the filter in the debug window to see what it looks like. You can copy and paste the string into the Filter property in the form's property dialog. Sometimes, you will get better error messages. You can also adjust the filter here. Try adding yourtablename. in front of all columns. Try removing all the square brackets.