Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Why does this work? (Acc2K)

    I was trying to exclude several values from one field in a query Only one value is allowed in the field.

    This didn't work:
    Not "value" Or Not '"Value" Or Not "Value" (it would still return everything).

    Then I tried this, which works:
    Not "value" And Not '"Value" And Not "Value"

    I just wonder why the second one works. I thought the AND operator would only return values that were together in the same record.

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    KY USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does this work? (Acc2K)

    Give the example you were trying to exclude the employees Billy, Brian, and Jeff. You say,
    select * from MyTable where Name <> "Billy" or <> "Brian" or <> "Jeff"

    When the query gets to the first record, it sees "Brian." Brian does not = Jeff. Brian does not = Billy. So it fits. Because if you say OR, you are saying Anything besides Brian (which includes Billy or Jeff) or anything besides Billy (which includes Brian or Jeff), or anything besides Jeff (which includes Billy or Brian).

    When you say AND, you are saying, it can't be Brian, it can't be Jeff, it can't be Billy. Anything besides that is OK.

    Hope that helps.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Why does this work? (Acc2K)

    Activate WHERE regarding the field of interest and enter as criteria:

    NOT IN("Val1","Val2",...) where Val1, Val2,... are the values you want to exclude.

    By the way, AND NOT "Val1" is equivalent to NOT IN("Val1").
    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks!

    Thanks, I get it now.

Posting Permissions

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