Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Question Unanswered: Access won't delete correct values

    I keep trying to delete every record with a 487 in the XINVESTOR ID that IS NOT 144,145,149,152,153 in the XCATEGORY CODE.

    Below is one the queries I used. It seems to pull everything from 487 including the <> items.

    It will work if you use only one variable in XCATEGORY CODE 145.

    Can anyone help or at least shoot me in the right direction??

    DELETE FAB.[XCATEGORY CODE], FAB.[XINVESTOR ID]
    FROM FAB
    WHERE (((FAB.[XCATEGORY CODE])<>"144"
    Or (FAB.[XCATEGORY CODE])<>"145"
    Or (FAB.[XCATEGORY CODE])<>"149"
    Or (FAB.[XCATEGORY CODE])<>"152"
    Or (FAB.[XCATEGORY CODE])<>"153")
    AND ((FAB.[XINVESTOR ID])="487"));

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Access won't delete correct values

    Originally posted by itarin
    I keep trying to delete every record with a 487 in the XINVESTOR ID that IS NOT 144,145,149,152,153 in the XCATEGORY CODE.

    Below is one the queries I used. It seems to pull everything from 487 including the <> items.

    It will work if you use only one variable in XCATEGORY CODE 145.

    Can anyone help or at least shoot me in the right direction??

    DELETE FAB.[XCATEGORY CODE], FAB.[XINVESTOR ID]
    FROM FAB
    WHERE (((FAB.[XCATEGORY CODE])<>"144"
    Or (FAB.[XCATEGORY CODE])<>"145"
    Or (FAB.[XCATEGORY CODE])<>"149"
    Or (FAB.[XCATEGORY CODE])<>"152"
    Or (FAB.[XCATEGORY CODE])<>"153")
    AND ((FAB.[XINVESTOR ID])="487"));

    Thanks
    Try:

    DELETE FROM FAB WHERE (NOT (FAB.[XCATEGORY CODE] IN ("144","145","149","152","153")) AND (FAB.[XINVESTOR ID]="487"));

  3. #3
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Access won't delete correct values

    Originally posted by itarin
    I keep trying to delete every record with a 487 in the XINVESTOR ID that IS NOT 144,145,149,152,153 in the XCATEGORY CODE.

    Below is one the queries I used. It seems to pull everything from 487 including the <> items.

    It will work if you use only one variable in XCATEGORY CODE 145.

    Can anyone help or at least shoot me in the right direction??

    DELETE FAB.[XCATEGORY CODE], FAB.[XINVESTOR ID]
    FROM FAB
    WHERE (((FAB.[XCATEGORY CODE])<>"144"
    Or (FAB.[XCATEGORY CODE])<>"145"
    Or (FAB.[XCATEGORY CODE])<>"149"
    Or (FAB.[XCATEGORY CODE])<>"152"
    Or (FAB.[XCATEGORY CODE])<>"153")
    AND ((FAB.[XINVESTOR ID])="487"));

    Thanks
    Wouldn't you want to use "And" instead or "Or" in the Where clause?
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  4. #4
    Join Date
    Apr 2004
    Posts
    8

    Talking Re: Access won't delete correct values

    Originally posted by M Owen
    Try:

    DELETE FROM FAB WHERE (NOT (FAB.[XCATEGORY CODE] IN ("144","145","149","152","153")) AND (FAB.[XINVESTOR ID]="487"));
    You are the GRAND POOBAH!

    Thank you, it worked, I just had to add FAB.[XINVESTOR ID] after delete.


    Thanks again

Posting Permissions

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