Query excluding Null fields when it shouldn't

[mcgim1]

This is probably a stupid question but I'm having a blonde moment.

I have a simple query where I want to exclude records based on a field not equalling a certain value

My query contains <> "CANC" on the field I want to filter on. However it is also excluding records where the field is null. Obviously I can fix by having <>"CANC" or Is Null but it just doesn't make sense to me. <>"CANC" should not exclude Null records

I'm using Access 2002. Please help.

 

[dhookom]

Null is the same as "I don't know". You can't compare anything to "I don't know" with = or <>. For instance does your shirt contain the color "I don't know". You can convert nulls to a value and then compare like:
WHERE [FieldName] & "" <> "CANC"

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]