Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2009
    Posts
    30

    Unanswered: Query criteria with multiple "Not equal to"

    I have a situation where I have to pull data from a table through a query, but I have to exclude or filter out approximately 100+ different types of Clinic codes. I've included a simplified version of the SQL below, but there would be over 100 of the PTCLINIC codes that I would have to filter out. I know Access has its limits and it seems extremely inefficient for me to type this out and have it process in the criteria. There must be a simple solution, but I can't think of what that would be. Can I put the codes in a table and somehow compare it to that table and ask to exclude? If you ask me to write code, I may be a little lost on where to put that code. Unfortunately this is a text field opposed to numeric, so it makes it difficult for the few places I can select a range, like 039-072, I'll have to type them out individually. I cannot seem to find a solution anywhere I look, so I appreciate your help.

    SELECT P.PTCLINIC
    FROM P
    WHERE (((P.PTCLINIC)<>"002" Or (P.PTCLINIC)<>"006" Or (P.PTCLINIC)<>"171" Or (P.PTCLINIC)<>"181"));

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, you'd need to use AND rather than OR. The simpler SQL solution would be an IN() clause, but the best solution is the table you've described. Create/populate that table then use the unmatched query wizard to get the records that are not in that table.
    Paul

  3. #3
    Join Date
    Feb 2009
    Posts
    30
    Fantastic, you just saved me a lot of time trying to figure this out and I will use this in the future. Both worked great. Thanks so much, this site is very helpful.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

  5. #5
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1

    Access 2013 not like "" AND not like "" .... etc

    Hi

    I need to pull back records from one table, excluding records with certain values.

    In the query design screen I type in NOT "xyz" OR "ABC" and it does not like it.

    It seems to either miss out one of the conditions or ... well something.

    How can I achieve this?

    If I need to go into the SQL editor then so be it .. if you can help me with that syntax I would be grateful.

    Thanks in advance

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    NOT IN("xyz", "ABC")
    Paul

  7. #7
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Sorry for the last reply ... weekend was crazy...

    I have tried this in the Access Query designer, and it says data type mismatch in criteria expression.

    Surely this is a basic thing to do no?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is the data type of the field? The query runs without error without the criteria?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    switch to SQL view
    in the where clause add

    if numeric
    mycolumn not in (123,456)
    if string
    mycolumn not in ('ABC', 'DEF')
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Really appreciate your help here fellas ...

    In SQL view it currently says

    WHERE ((Not (Decommissions.Status)="out of scope" Or (Decommissions.Status)="Already decommed"));

    so I change to reflect your suggestion below, to:

    if numeric
    mycolumn not in (123,456)
    if string
    mycolumn not in ('ABC', 'DEF')

    The field contains text only, which notes the server status.

    I am not used to preparing SQL but I would like to practice and this is a good starting point. What should this syntax be?

    thanks so much in advance.

  11. #11
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    WHERE (if numeric (Decommissions.Status) not in (123,456) if string (Decommissions.Status) not in ('out of scope', 'Already decommed') ; something like this?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if numeric
    Code:
    mycolumn not in (123,456)
    if string
    Code:
    mycolumn not in ('ABC', 'DEF')
    strings must be delimited by pairs of ' or "

    if date
    Code:
    mydatecolumn not in ('#2014/01/26#', '#2013/12/25#')
    dates must be delimited by # AND be in ISO YYYY/MM/DD or US format MM/DD/YYYY


    so its going to be something like:-
    Code:
    WHERE (Decommissions.Status not in ('out of scope', 'Already decommed')) ;
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    so good of you to reply ....

    Error now is 'characters found after SQL statement' .. .did I paste it wrong?

  14. #14
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Yep .. I flipping pasted it wrong .... left the semi colon in there

    SELECT Decommissions.[CI Name], Decommissions.[Physical Or Virtual], Decommissions.Status
    FROM Decommissions
    WHERE (Decommissions.Status not in ('out of scope', 'Already decommed')) ;

  15. #15
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    left the semi colon in there after the From ....

    anyway now this is working beautifully ... thank you SO SO much .

    Have a great day ... I'll be back that is for sure.

    By the way, does the forum have a view on why Access 2013 no longer has Collect Data via email ... like Access 2010 used to have??? My own view is not publishable ...

Tags for this Thread

Posting Permissions

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