Not operator

[Melanie]

Can someone please explain to me why this problem is occurring to me when I am working with a text field in a query.

For example if the field contains first names and I enter in the criteria "john", and run the query, then the result is all the records that contain the first name john.

HOWEVER, when I try using any type of not condition (ie not, <>, not like, not in) I receive no records for the output, and there are certainly other first names in the table.

Please help.

 

[Joe4]

It should work if you enter the criteria like:

Not "John"

If it doesn't, be sure to check that you don't have any other criteria interfering with this criteria.

 

[Melanie]

I had tried entering that, but it did not work. And that is the only criteria I have with the query.

Do you have any other suggestions?

thanks.

 

[Joe4]

What happens if you create a brand new query with only this one field in it, then type in this one criteria. Does it work?

If not, can you specify the field specs of the field you are trying to do the query on and list some samples of the data?

 

[Melanie]

I think I was able to figure it out.

All the other records are blank. For example the field either contains the name "John" or is null. So in the criteria I simply typed "is null" and it gave me all of the non-john records.

But still, I thought that if I used the not criteria that should give me the same answer...

 

[Joe4]

No, unfortunately Null fields are handled differently. If you want to check for a value not being equal to something, and there may be null fields, then you need to use format:

Not "criteria" Or Is Null