Filtering Only Works Intermittently

[dillopk]

I am an Access amateur with a condo association database; one of my tables contains vehicle information, about 1,300 rows. Each row or vehicle has a field for a street address, most of which (but not all) contain data. There are 500+ unique street addresses, and multiple vehicles (rows) may have the same street address.

My problem is that when I am in the normal Datasheet view of the table and I try to apply a filter to the street address column, sometimes it works fine but sometimes it doesn't. When it is working, I get a drop-down list showing check boxes for "Select All" and for all the street addresses, and I can select or de-select the ones I want and everything is cool. But other times (unpredictably) the drop-down list that appears when I click on the filter icon at the top of the column has only three items with check boxes - "Select All", "(Blanks)", and a third check box with no label - just a check box with no text to the right of it. All three are checked if I clear all filters. If I un-check "Select All" and then check only the "(Blanks)" box, Access hides everything except a row with all fields empty and "New" in the ID column, same as is normally displayed at the very bottom of a table. If I un-check "Select All" and check only the unlabeled check box, I get a pop-up error message - "The expression you entered contains invalid syntax / You may have entered a comma without a preceding value or identifier."

Also when I click the Filter icon, the Sort Ascending and Sort Descending options are always in the box that appears; that's my work-around for the filtering problem - sorting lets me see all the vehicle rows for one address next to each other. I just need to ignore the rows containing the other addresses that are displayed above and below the address that interests me.

One day it works, next day it doesn't. Sometimes after I mess around with other tables, forms, etc. it starts (or stops) working. I see no pattern. Any ideas? I am running Access 2013 on Windows 7.

[alansidman]

Instead of filtering a table, why not write a query to give you the same results.

[ssanfu]

I agree with Alan. Don't work directly with tables.

I would suggest you use queries/bound forms and never add/edit/filter/delete data directly in tables.

It would help you a lot to work through these tutorials. Take the time to actually DO the examples..


If you do the tutorials and still have problems, post back with a copy of your dB (remove/change sensitive data and only need 10 - 20 records) for analysis.

Good luck.