Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    1

    <> criteria for several 3 digit codes in 2 fields

    I have a query Im working on. It has several fields three of which are cat1 cat 2 and quantity.

    In cat1 and cat 2 I want to delete seven, three digit codes from the query while quantity remains >0. I also want to display all null values in cat1 and 2. SO my theory was to type:

    Not "333" And "334" And "335" etc till all 7 codes for both cat 1 and 2 and place the criteria for quantity as >0. Next row down for criteria I would put Is Null in cat 1 and 2 and >0 for qty. However when I do this it never works. It usually ends up deleting unecassary codes or not deleting any codes. All I can seem to do is delete one code instead of the desired multiple. So If I do -Not "333"- for just cat1 using no other codes or fields then it works. But, I cannot seem to get rid of any other codes in the query by specifying the criteria.

    Ex of not working criteria set forth:

    field: Cat1 | Cat2 | quantity

    criteria: Not "333" And "334" And...(till all 7 are entered)| Not "333" And "334" And...(till all 7 are entered)| >0






    I must say Im at a bit of a loss and would greatly appreicate any and all help! Thanks!

  2. #2
    Join Date
    Sep 2006
    Location
    Manchester, England
    Posts
    13
    I'm not sure I fully understand your question so let me try explain how I understand it.

    You want to delete records from atable where either of the cat fileds is equal to a value you specify in a list AND where the quantity is greater than zero?

    If this is the case something like this will do it :

    Code:
    DELETE FROM myTable
    WHERE qty > 0 AND
    (
      OR cat1 IN (233,232,445,343,565,343)
      OR cat2 IN (233,232,445,343,565,343)
    )
    as for getting NULL values try

    Code:
    SELECT * FROM myTable 
    WHERE isNull(cat1) = true OR isNull(cat2) = true

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

Similar Threads

  1. Query Criteria
    By Texaine in forum Queries
    Replies: 1
    Last Post: 01-24-2018, 02:36 PM
  2. Date Criteria
    By tcollins02 in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 08:27 AM
  3. Query Criteria
    By jena in forum Access
    Replies: 1
    Last Post: 08-03-2008, 04:08 AM
  4. Query Criteria
    By jena in forum Queries
    Replies: 1
    Last Post: 04-29-2008, 11:00 AM
  5. Criteria Definition
    By claing in forum Access
    Replies: 1
    Last Post: 12-19-2005, 07:46 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