Does Not Contain Filter: Having Multiple Entries (picture)

[samanthaM]

I am working with a large dataset. In the field "owner_name" I need to filter out all entries that contain a bank or mortgage company. The problem is the "does not contain" filter only lets me filter out one bank or mortgage company at a time. I need to filter out many banks simultaneously.



For instance I need to filter out the following all at one time:
Bank of America NA
Bank of New York Mellon
Bank Of New York Trust Co
Bank Of New York Trust Co Na
Bank Of New Yrok Mellon
....and many more

However, the "does not contain" filter seemingly only lets me filter out one at a time. See image below for illustration.




Please let me know if clarification is needed.

[ssanfu]

Do you have a "Type of Owner" field? ie: Bank, Mortgage Company or "Person"

[samanthaM]

Do you have a "Type of Owner" field? ie: Bank, Mortgage Company or "Person"

No. The data comes straight from the county. It just has the Owner Name field. But within the owner name field many of these bank owned name properties are repeated e.g. "City Bank, Bank of America NA" etc

[ssanfu]

It sounds like this is (will be) an on-going process. Long term, I would recommend developing form/code to make it easier to filter the dataset.

*** Short term
Try this:
Open the query. Look at the record count (bottom of the query). Then open the query in design view.
In the Criteria row of the "Owner_Name" column, put

Not Like "*bank*"

Run the query. Look at the record count. The record count should be smaller.
then change the criteria to:

Not Like "*bank*" AND Not Like "*Mortgage*"

The problem here is, if any person (owner_name) has a name something like "Fairbanks" or "Banks", they will be filtered out also.


***Long term:
Create a table of names you want to filter on... the banks and Mortgages, with a Boolean (Yes/no) type field. the name only has to be entered once..
Create a form where the record source is a query (queries can be sorted) on the above table. Select the records you want to not include. Or use a multi-select list box to select the bank/mortgage names.

Use VBA to create a WHERE clause with all the names.

Use code behind a button to open a query, using the where clause.

[samanthaM]

It sounds like this is (will be) an on-going process. Long term, I would recommend developing form/code to make it easier to filter the dataset.
*** Short term

In regards to the Short Term Solution. I actually have a full list of all the banks and mortgage companies. I could use "Not" and directly enter in each of the bank names, rather than use "Not Like" and risk owners names similar to bank omitted e.g. "Eubanks" or "Fairbanks".

The problem is how do you run a list of multiple items with the "Not" criteria? I attempted doing multiple entries but it gives me an invalid syntax error when I try and run the query (image below). Here is how I am trying to run it within access for the query criteria.
Criteria: Not "Bank of America NA" Not "Bank Of North Texas" Not "Federal National Mortgage Assn"
which again resulted in an invalid syntax error. Do I need to stick the rest of the banks beyond the first in the "Or" section?


The long term solutions utilizing VBA is well beyond my capabilities, I am a novice with Access.

[ssanfu]

Criteria: Not "Bank of America NA" Not "Bank Of North Texas" Not "Federal National Mortgage Assn"

You need to add "AND" between the names :

Criteria: (Not "Bank of America NA") AND (Not "Bank Of North Texas") AND (Not "Federal National Mortgage Assn)"

The parenthesis might not be needed, but it is easier to read and understand.

Be aware that there is a limit to the number of characters you can type into the criteria - but I don't know what the limit is in Acc2010 (I have A2000)

[ssanfu]

This is one way to be able to filter a recordset.
(the attached dB is A2k format)

[samanthaM]

You need to add "AND" between the names

Thanks for the tip. I Tried using "or" in the criteria and that seemed to work. Is this the correct usage of the "or" function in this scenario? See image below


Thanks

[ssanfu]

Nope. Execute the query (shown above) and look for "City Bank". It will still be in the list.

Or add just the first criteria (the one with the "NOT"). Check the number of records returned. Then add the 2nd line (shown above). Execute the query and check the record count. It shouldn't have changed.

You can add the first criteria for "Owner_Name" (Not "Bank of America NA"), then add "Owner_Name" a 2nd time, add the 2nd criteria (Not "Bank of New York Mellon"), then add "Owner_Name" a 3rd time and the criteria for that column,....
So you will have 3 columns of "Owner_Name".
Access will then change it to one column "Owner_Name" with the criteria that looks like: (my example data)

<>"Bank of Commerce" And <>"Bank of Hydro" And <>"Bank of the Panhandle"

Switch to SQL view to see how Access writes the query.