VBA to Filter for exact match

[loonette]

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?

Private Sub CheckFilter()
 
  Dim strFilter As String, strOldFilter As String
 
    strOldFilter = Me.Filter
 If Me!filtercat > "" Then _
        strFilter = strFilter & _
                    " AND ([SEitem#] Like '" & _
                    Me!filtercat & "*')"
    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub
 

Thank you,
Lori

[Seth Schrock]

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.

strFilter = [SEitem#] = Me!filtercat

I don't code very many filters so my syntax could be wrong, but that is where your problem lies.

[NeoPa]

Lines #6 to #9 in your code should be :

    If Me.FilterCat > "" Then _
        strFilter = strFilter & " AND ([SEitem#] = '" & Me.FilterCat & "')"

[loonette]

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

[Seth Schrock]

What does the error message say?

[loonette]

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

Lori

[NeoPa]

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.

[loonette]

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

[NeoPa]

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.

[loonette]

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

[NeoPa]

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?

[loonette]

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

[NeoPa]

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 :-)