By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,663 Members | 1,843 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,663 IT Pros & Developers. It's quick & easy.

VBA to Filter for exact match

loonette
P: 17
I need help with some code. I am using this code to filter for a catalog number([SEitem#]) in a form. I would like it to filter for the exact match. When it filters right now if you put a "1" in the [filtercat] text box it filters for everything that starts with "1", how do I change the code to filter for the exact match?

Expand|Select|Wrap|Line Numbers
  1. Private Sub CheckFilter()
  2.  
  3.   Dim strFilter As String, strOldFilter As String
  4.  
  5.     strOldFilter = Me.Filter
  6.  If Me!filtercat > "" Then _
  7.         strFilter = strFilter & _
  8.                     " AND ([SEitem#] Like '" & _
  9.                     Me!filtercat & "*')"
  10.     'Debug.Print ".Filter = '" & strOldFilter & "' - ";
  11.     'Debug.Print "strFilter = '" & strFilter & " '"
  12.     'Tidy up results and apply IF NECESSARY
  13.     If strFilter > "" Then strFilter = Mid(strFilter, 6)
  14.     If strFilter <> strOldFilter Then
  15.         Me.Filter = strFilter
  16.         Me.FilterOn = (strFilter > "")
  17.     End If
  18. End Sub
  19.  
Thank you,
Lori
Jan 13 '12 #1
Share this Question
Share on Google+
12 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
The reason that it is returning everything that starts with a 1 is because of the word like in line 8 and the "*" in line 9. So a 1 in SEitem# is like 1, 10, 100, 155, etc. You want it to equal Me!filtercat. I believe that the fix would be the following for lines 7 through 9.

Expand|Select|Wrap|Line Numbers
  1. strFilter = [SEitem#] = Me!filtercat
I don't code very many filters so my syntax could be wrong, but that is where your problem lies.
Jan 13 '12 #2

NeoPa
Expert Mod 15k+
P: 31,712
Lines #6 to #9 in your code should be :
Expand|Select|Wrap|Line Numbers
  1.     If Me.FilterCat > "" Then _
  2.         strFilter = strFilter & " AND ([SEitem#] = '" & Me.FilterCat & "')"
Jan 13 '12 #3

loonette
P: 17
NeoPa,

Thank you for your help. I put the code in that you gave me and I am now getting an error on Line #15 & #16 of my code. Any thoughts?

Thanks!
Lori
Jan 14 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,951
What does the error message say?
Jan 14 '12 #5

loonette
P: 17
It doesn't say anything, it just goes into debug and highlights those two lines.

Lori
Jan 14 '12 #6

NeoPa
Expert Mod 15k+
P: 31,712
That's strange Lori. The two lines are separate as you've shown them here and when code stops it always stops on a single VBA command (IE. a single line or multiple lines if a single command is spread over them). Not only is the code on your lines #15 and #16 two separate commands, but it's also good, bug-free, code.

I have come across situations where the debugger gets confused and thinks it needs to stop simply to debug. This may be one of those. Try recompiling and saving the database then closing Access completely and opening it up again and see what happens.
Jan 14 '12 #7

loonette
P: 17
NeoPa,

I tried compact & repair the database and it didn't fix the error. I apologize, it is giving me an error message it says "Runtime Error 2001, You canceled the previous operation" Any more thoughts?

Lori
Jan 14 '12 #8

NeoPa
Expert Mod 15k+
P: 31,712
Lori, I can't see how that can make sense. First you say there is no error message, then you say you tried C&R and the error's still there - now with an error message. Perhaps you could explain so that I can understand.
Jan 15 '12 #9

loonette
P: 17
NeoPa,

I apologized in my previous post. It WAS giving me an error message originally, I just wasn't looking in the correct place for the error message. I thought the error message would come up when it highlighted the code, not before I hit the debug button. Sorry for any confusion.

To clarify, when I try to filter for the exact match it is giving me an error which says "Runtime Error 2001, You canceled the previous operation". I then click on the debug button, the programs then highlights lines 15 & 16 of the code.

Thank you,
Lori

Lori
Jan 15 '12 #10

NeoPa
Expert Mod 15k+
P: 31,712
That's much clearer. Unfortunately we still have problems. That error message is particularly uninformative. It only tells me there may be some form of corruption somewhere. We also have the issue I mentioned in post #7, where your explanation doesn't match your posted code. Even with a corruption it can still only select a single line (or command) of code. Is your code exactly as posted in post #1?
Jan 15 '12 #11

loonette
P: 17
Ok, I think I have it narrowed down. When I received the error message the first time it highlighted both lines. Now when I do it is only highlighting line 16. This is so weird! Let me know if that helps....

Thanks!
Lori
Jan 16 '12 #12

NeoPa
Expert Mod 15k+
P: 31,712
It certainly makes more sense Lori, but as I said earlier, this error message is largely unhelpful. When I get something like that I have to play it by ear to a certain extent. What I try to do (if I can attempt to put some ideas into a coherent set of steps to try) is to make a change to the code (Nothing that has any real effect, but enough for Access to realise that a save is required) then, assuring that no lines are breakpointed anywhere, compile then save the module again. Then I close and reopen the database and hope that the problem has disappeared. If it hasn't then I can't help you.

The problem is not with any of your code I'm confident to say, but probably something you've done earlier, or that Access has mistakenly tracked you as having done earlier. I've had similar situations where typing in the same code again exactly as it was before, but after deleting the original and offending line, has resulted in the problem disappearing and all being hunky-dory going forward. That's another thing to try if the other advice yields nothing.

The issue doesn't appear to be very logical, so I can't offer any logical or reliable solution I'm afraid. Good luck anyway :-)
Jan 16 '12 #13

Post your reply

Sign in to post your reply or Sign up for a free account.