Null Value ‘Gotcha’ in Query

Topic

New Reply

SELECT Vendor, Qty, Item FROM POs
WHERE (
Item Like”*This*” Or Item like “*That*” AND
Vendor Not Like “*(Tng Vendor)*” or Vendor = Null
);

If you set up a query to filter out records with a certain value in a field, you are, in effect, creating a filter that filters out not _only_ that value, but any records that might have NULLs in that field!!!!!
I was unaware of that, and I think this is a significant “gotcha” that probably should be widely known, and maybe, it is!
I solved the problem in the Vendor line above by putting in the actual check for null besides the check for not (the name of our usual training vendor).
(Vendor fld. is text, allow zero length = no, required = no, Access 97 db on Win95.)
I had to learn it the hard way, on a small, inconsequential (“just for our office”) project, but I wonder how many other, more important queries I might have let out the door without regarding this!
Am I the last person in the Access World to know this? Aaauuuughhhh!

thx
Pat

Reply | Quote

Replies

Hey,

You are not the only one who found out the hard way.
In a lot of cases I even choose to program the database activities (UPDATE, INSERT and DELETE) completely in a class module to ensure every field is filled with at least an empty string (“”) or 0.
Yes, I also think this is one of the famous ‘Gotcha’s’.
Beware all, there are more…..

Reply | Quote

> Yes, I also think this is one of the famous ‘Gotcha’s’.
> Beware all, there are more…..

……. Hmmm, wonder if someone has created a “gotcha” list!
……..Thanks for your kind response,

Pat (using the dots to indent!)

Reply | Quote

I think you might want to check for Vendor Is Null. Nothing can equal Null, although you can use the equal sign in an expression assigning a null value to something. Even Null can’t equal Null. Try it sometime and you’ll discover the expression returns a false.

Reply | Quote

> I think you might want to check for Vendor Is Null. Nothing can equal Null …. Try it sometime and you’ll discover the expression returns a false.

Charlotte,
………… Thanks, duh! after I was just talking in another thread about how I had realized that my code had broken on it in VB (actually, it did not crash or stop at that point, but of course, didn’t evaluate).
………… However, get ready for this: In my SQL window I am displaying the incorrect “=” sign, and switching to the graphic interface I get the “is null”. I switch back and forth and it remains the same, and works.
………… However again, I just went back and corrected it to follow the rules.
………… Thanks for pointing this out!

Pat

Reply | Quote

2000 is pretty forgiving in some areas, but it’s always better to do it right and avoid possible consequences.

Reply | Quote