"not like" query results in extra rows being removed

[pbenson]

I'm a novice at Access and am having trouble with a Not Like query. I'm trying to remove rows with the term "Accrual" from a table. I know there are 16 rows that have the term Accrual, but when I do a Not Like "Accrual" query it removes 80 rows. If I change the query to Like "Accrual" it will show the 16 rows. Why is the Not Like query removing more rows?

I appreciate any help/guidance for this rookie.

[missinglinq]

In the Query Design Grid, in the field's Criteria, use

<>"Accrual"

The SQL, assuming the field in question is called TargetField, would be

SELECT YourTableName.Field1, YourTableName.Field2, YourTableName.TargetField
FROM YourTableName
WHERE (((YourTableName.TargetField)<>"Accrual"));

Welcome to Bytes!

Linq ;0)>

[TheSmileyCoder]

From what your describing, im guessing the field with the value "Accrual" also contains records that are null. You can read more about querys with fields that can be null elsewhere, but the quick fix to your issue:

Change the criteria to:
<> "Accrual" Or Is Null

[NeoPa]

When you use "Like" (or "Not Like") then you need to use some wildcard characters. Otherwise it is equivalent to = (or Not =). Your current SQL is really saying :

"<>'accrual'"

If you'd like to specify all records where a particular field doesn't contain the string "accrual", then you need something like :

WHERE [FieldName] Not Like '*accrual*'

Also check out ANSI Standards in String Comparisons for a full understanding of which characters to use for this in your situation.