Query treating blank field as equal to criteria (1 Viewer)

[AndrewS]

I have a table as follows

WorkorderListID
Workorder
Workorder_Name
Wo_Status

All Short Text, except WorkorderListID which is autonumbered.

Wo_Status contains one of two entries: N or C

Running the following query, I noticed that one expected record was not being returned. This seems to be because for this one record Wo_Status is blank. If I remove the criteria from the query, the record is returned.


SELECT tbl_WorkorderList.Workorder, tbl_WorkorderList.Workorder_Name, tbl_WorkorderList.Wo_Status
FROM tbl_WorkorderList
WHERE (((tbl_WorkorderList.Wo_Status)<>"C"))
ORDER BY tbl_WorkorderList.Workorder;

This is probably Access101, and I'm missing something obvious, but I would have thought that a blank field was not equal to C.

I'll workround it by checking that the field is filled on the form, but am puzzled why it's treating blank as equal to C.

 

[plog]

Actually its a 200 level math class, and its non-intuitive: Remember how 0 is different from the empty set? The set that contains no values? Well, here's a practical application of that.

Think of it this way, Null has no value, it cannot be compared to anything in any way except Null. So when you compare it to anything, even negatively, it will return false.

So, these 2 comparisons:

Null=0
Null<>0

Both return false. The only way to achieve true is this:

Null = Null
IsNull(Null)

That lesson behind us, why are you not using a Yes/No field for that? Is Null a valid value for that field?

 

[AndrewS]

Thanks!

I hate Null

 

[isladogs]

And if two fields X and Y are both Null, is X = Y?

The answer is of course No as no two nulls are the same

There ain't nuffink like a null

 

[MarkK]

So, these 2 comparisons:

Null=0
Null<>0

Both return false. The only way to achieve true is this:

Null = Null
IsNull(Null)

Technically incorrect except for IsNull(Null). In the immediate pane you get...

? null = 0
Null
? null <> 0
Null
? null = null
Null
? IsNull(Null)
True
? Nz(Null, "Holy smokes, that was a null")
Holy smokes, that was a null
? "Start" & Null & "End"
StartEnd

Any expression with a Null operand returns Null, which is not the same as False.
Three ways to clean up a null are IsNull(), Nz(), and the "&" string concatenation operator.
Mark