Results 1 to 4 of 4
  1. #1
    Mabid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2017
    Posts
    10

    Query Criteria

    I have a field has many categories , so I did a IN to select few, now I want to exclude these in other new field in Query , tried to use NOT in didn't work,

    Example field name in Query Field i criteria set in ("a","b")
    now want to exclude a and b to select all other from the list


    need help

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ??? I don't understand your post.
    Please try to describe your table, the field (hopefully not a lookup field), and what you are trying to accomplish in more detail.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You must have done something wrong, because this does work in the Criteria field for a field in a query:
    Code:
    Not In ("a","b")
    Note though, that you wouldn't want to put that criteria on a query that is based on your first query that only included a and b.
    Because if you have a query that only included a and b, and then do another query using that first query as your source, it won't return anything, because you already have excluded anything not equal to a and b.
    (What you would really be setting criteria that says "return all records where the field is "a" or "b" AND return all records where the field is NOT "a" or "b", and it is impossible for any record to satisfy both of those conditions at the same time).

    So your new query would need to be based on the same table or query that the original one was based on.

  4. #4
    Mabid is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2017
    Posts
    10
    Thanks I got the idea from you reply and got the results, I. E. I did three quarries
    A. to retrieve a&b (customer billing)
    B. To retrieve other than a&b(customer billing)
    C. To retrieve collections
    D. Cross tab based on a, b, c above
    Two problems
    1. C above gives amount by transactions date, I need by xutomers
    2.D,cross tab, results in column headers as dates, I don't know how to change column header in a cross tab

    Thanks for your detailed guidelines

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-02-2015, 12:45 PM
  2. Replies: 1
    Last Post: 11-13-2014, 11:34 PM
  3. Replies: 4
    Last Post: 08-30-2014, 10:23 AM
  4. Replies: 5
    Last Post: 09-20-2012, 03:27 PM
  5. Replies: 5
    Last Post: 08-02-2012, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums