Query: Pulling out all Records That Contain Specific Text

[samanthaM]

In the Owner Name field, I am looking for a way to pull out all estates. They are abbreviated with "EST" or "Estate". The problem is throughout the database, if you do a simple filter for "contains est" it will include names such as "Forester" or "LHB Investments".

How do I query, or filter this database such that it only returns the estates abbreviated with "EST" or "Estate"? Please see the image below that clearly illustrates this problem.





Thanks in advance.

[RuralGuy]

Include the leading space " " in your filter. " EST" OR " ESTATE"

[samanthaM]

@RuralGuy, Thank you. Is there a way to save out your filter results to a query to make permanent? Or do you just have to export the database with the filter applied and reimport it?

[RuralGuy]

You can make a static query that just pulls these records. In design view of the table enter the criteria discussed and include this field twice in the design putting your 2nd criteria on a different criteria line which will make it an OR. If they are on the same line it would be an AND.

[samanthaM]

@RuralGuy, Regarding your orignal suggestion for the filter. I attempted that, the problem is it is only returning the "est" estates. It is not returning any record with the word "estate" in it. What did I do incorrectly?

[RuralGuy]

I thought you were going to do a static query?

[RuralGuy]

The filter wizard has limitations as you've discovered.

[samanthaM]

I thought you were going to do a static query?

Yes, I am attempting to. Is this what you meant by having the field twice? Also for the criteria how do I enter it in? When using a filter you simply use Contains EST or Estate. You cant type "Contains EST" into the query criteria it gives you an error.

[RuralGuy]

Try = " EST" ... and = " Estate"
Note: it is *not* case sensitive.

Actually I just realized that won't work. We'll need to use InStr()

[RuralGuy]

Create another column with the field defined as InStr([Owner_Name]," Est") and a Criteria of >0.
Create yet another column with the field defined as InStr([Owner_Name]," Estate") and a Criteria of >0.
I tested this and it works.

[samanthaM]

@RuralGuy

Perhaps I did something incorrectly, but it is not returning all the "Est" and "Estates" within the database. It is returning some of them. Furthermore these results have made it apparent I need to filter to remove the word "real estate".

In the output shown below, as I mentioned, most if not all of the "Est" estates have been removed. For example Bill, Johnson Est or Jackie, Thompson Est.

[RuralGuy]

Show me what you have in the Field row of those two columns please. It does not show completely in the picture. You should be able to use a Not InStr() function to eliminate the "Real Estate" rows.