Results 1 to 6 of 6
  1. #1
    PatriciaWilliams
    Guest

    Null Value 'Gotcha' in Query

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null Value 'Gotcha' in Query

    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.....

  3. #3
    PatriciaWilliams
    Guest

    Re: Null Value 'Gotcha' in Query

    > 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!)

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null Value 'Gotcha' in Query

    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.
    Charlotte

  5. #5
    PatriciaWilliams
    Guest

    Re: Null Value 'Gotcha' in Query

    > 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

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null Value 'Gotcha' in Query

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •