How do I exclude records with the a text string

[JRCBoston]

Hi-

I have a field with text of varying lengths. I want to exclude all records where the text in a certain field has the word transfer. How would I do that?

I'm trying to use a Select query where it excludes the record if the criteria says - Not like "transfer" - but that does not seem to work.

Thank you.

Jonathan

 

[Joe4]

How about using this criteria:

Not Like "*transfer*"

 

[afs24]

I'm trying to use a similar function but I don't want the query results to pull multiple names. This is what I'm trying:

Not Like "J2" or Not Like "BV" or Not Like "HO"

 

[Joe4]

Two things:

1. You want to use "AND", not "OR". If you use "OR", every record will be returned because it will return the record if any one of the three conditions are met. Since the conditions are all different, no records will be exlcuded. "AND" will only return the record if ALL three conditions are met.

2. In order for the conditions you have written to be met, the entries would have to be exactly "J2" or "BV" or "HO". If you are looking for this to exist anywhere in the string, you need wildcards, i.e. "*J2*", "*BV*, "*HO*".
If you just want to exclude records the begin with these values, you would only need the wildcard at the end. For example,
Not Like "J2*"
would exclude things like "J2", "J234", "J2 ABC" but not "AJ2"

 

[afs24]

So, for example:

Column 1 Column 2
J2 6
AA 5
BV 5
HO 4
BB 11

I want the average excluding anything with J2 or BV or HO. My average should be 8. 5+11/2=16. How would I write this in a query? thanks

 

[Joe4]

I did this using two queries, one to select the records to include, and one to do the average.

Query 1
Include both Column 1 and Column 2. For your criteria under Column 1, use:
Not Like "J2" And Not Like "HO" And Not Like "BV"
Also, add a calculated field that simply gives all the matching records the same value, something simple like this will do:
Include: "Yes"

Query 2
Build this query using Query 1 as your source. Only select the "Include" field and the Column 2 field (do NOT include Column 1).
Then, click on the Totals button (looks like a Sigma).
Then, under Column 2, on the Total line, change "Group By" to "Average" (can select from the drop down box).

Now if you view your query, you will see that this will give you your average.