Compairing a string value to a null value in a string field.

  • Thread starter Thread starter Andrea
  • Start date Start date
A

Andrea

Hello!

I'm using recordsets to compair to tables with identical
fields. When I check to see if two text fields are not
equal, it doesn't recognize that a null value is not equal
to a text value. The condition works properly for
compairing date fields, boolean fields, and string fields
that are each storing text. It just seems to be
struggling with the null value.

Can someone explain this to me???

Thanks in advance!
Andrea
 
When I check to see if two text fields are not
equal, it doesn't recognize that a null value is not equal
to a text value.

Can someone explain this to me???
Click to expand...

NULL is a wierd beast. The way I think of it is that a NULL value
means "this value is unknown, it could be anything, I simply don't
know what".

Therefore, NULL is not equal to anything - nor is it UNEQUAL to
anything. If its value is unknown, then it might be anything - it
*might* be equal to the value that you're testing, you just don't
know!

In practice, ANY comparison: equal, unequal, greater, less - to a NULL
returns neither True nor False, but NULL.

To compare two fields and have them return False if one is NULL and
the other isn't, you can use a syntax like

Nz([Field1], "") <> Nz([Field2], "")

This assumes that the Allow Zero Length property is false on both
fields; if "" is a valid non-NULL value, then you will need to use a
riskier syntax:

Nz([Field1], "This Value Will Never Occur") <> Nz([Field2], "This
Value Will Never Occur")

or else

[Field1] <> [Field2] OR (Field1 IS NULL AND Field2 IS NOT NULL) OR
([Field2] IS NULL AND Field1 IS NOT NULL)
 
Well, that explains it! Thanks so much for the info!

Andrea
-----Original Message-----
When I check to see if two text fields are not
equal, it doesn't recognize that a null value is not equal
to a text value.

Can someone explain this to me???
Click to expand...

NULL is a wierd beast. The way I think of it is that a NULL value
means "this value is unknown, it could be anything, I simply don't
know what".

Therefore, NULL is not equal to anything - nor is it UNEQUAL to
anything. If its value is unknown, then it might be anything - it
*might* be equal to the value that you're testing, you just don't
know!

In practice, ANY comparison: equal, unequal, greater, less - to a NULL
returns neither True nor False, but NULL.

To compare two fields and have them return False if one is NULL and
the other isn't, you can use a syntax like

Nz([Field1], "") <> Nz([Field2], "")

This assumes that the Allow Zero Length property is false on both
fields; if "" is a valid non-NULL value, then you will need to use a
riskier syntax:

Nz([Field1], "This Value Will Never Occur") <> Nz ([Field2], "This
Value Will Never Occur")

or else

[Field1] <> [Field2] OR (Field1 IS NULL AND Field2 IS NOT NULL) OR
([Field2] IS NULL AND Field1 IS NOT NULL)



.
Click to expand...
 
Back
Top