CODEWITHSUNDEEP
vbams-accessfilter
l.wach
l.wach

Reputation: 67

Access Me.Filter stopped working

I have a realy strange problem with a filter in Access 2010. The filter worked great for the past days but today I noticed it stopped working (filters out everything) it's a pretty simple Me.Filter. I resetted the VBA code to a state where I know it was working but it still refuses to work as expected. this is an example filter that I am using:

Me.Filter = (Hersteller LIKE '*Test*' OR ID LIKE '*Test*' OR Typ LIKE '*Test*' OR Nennmaß LIKE '*Test*' OR Herstellertyp LIKE '*Test*') AND Status = 'Einsetzbar' AND ID is not null

Of course it's activated afterwards Me.FilterOn = True

Seeing that I tried the my old working code it must be something that I changed in the form or the backend table. Unfortunatly I didn't change anything that I can imagin having an impact on filtering. Column names etc. are the same... What makes it wierder is that my date filter Me.Filter = (Datum > #01/18/2015#) OR (Datum > #01/18/2015#) works perfectly. Any idea would be appreciated!!

Upvotes: 0

Views: 1631

Answers (2)

l.wach
l.wach

Reputation: 67

I figuered it out! I have no idea why it worked before and not now but using * as wildcard doesn't work for my version (Access 2010) I had to change every * in my filter to a % and now it finnaly wokrs! Me.Filter = "(Hersteller LIKE '%test%' OR ID LIKE '%test%' OR Typ LIKE '%test%' OR Nennmaß LIKE '%test%' OR Herstellertyp LIKE '%test%') AND ID is not null"

Upvotes: 1

MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

Notice how the last part of the code is grey? It's due to a single apostrophe outside of a string being evaluated as a comment.

Try:

Me.Filter = "(Hersteller LIKE '*Test*' OR ID LIKE '*Test*' OR Typ LIKE '*Test*' OR Nennmaß LIKE '*Test*' OR Herstellertyp LIKE '*Test*') AND Status = 'Einsetzbar' AND ID is not null"

Your current code's filter evaluates to: (Hersteller Like, which apparently is valid but because nothing follows the Like, then no records can meet the condition and no records can be returned.

The reason Me.Filter = (Datum > #01/18/2015#) OR (Datum > #01/18/2015#) works is because there are no apostrophes cutting off part of the clause.

Per MSDN:

The Filter property is a string expression consisting of a WHERE clause without the WHERE keyword.

So anytime you are using Me.Filter = the following clause should be encapsulated in double quotes to ensure it evaluates as a string, which is what .Filter is expecting.

Upvotes: 1

Related Questions