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

    Unanswered: Access Query problem

    Hi I am building a query in Access with 2 conditions (where criteria). The query runs fine but is giving the wrong result. I am asking it to exclude (<> "XYZ")some values from a variable but it is still outputting me those variables. Is there a limit as to how many variables we can specify in the Where criteria?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is the actual WHERE clause in the query? I suspect you may have an OR instead of an AND.
    Paul

  3. #3
    Join Date
    Dec 2003
    Posts
    24

    Talking

    There is. Its about a thousand.
    But remember if your variables is growing large then you need to think of changing the query.
    say if you are trying to check
    Code:
    a<>val1 AND a<> val2 AND a<>val3....
    could be replaced with
    Code:
    a NOT IN [val1,val2,val3,...]
    or you could query a Query

    post your SQL so we could see what's wrong..

  4. #4
    Join Date
    Feb 2009
    Posts
    28
    HI thanks for the quick responses. Kumar I will try your method in the mean time, here is my where clause


    The others dont appear. Market sector desc works fine too. Thanks
    Last edited by kvkanuri; 03-06-09 at 11:17.

  5. #5
    Join Date
    Feb 2009
    Posts
    28
    I am having problems with NOT IN too. Is this right?

    It gives me a Syntax error
    Last edited by kvkanuri; 03-06-09 at 11:16.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The problem with your original effort is that you have a mix of AND's and OR's, but haven't used parentheses to set them off. In other words, this:

    A AND B OR C

    may be treated differently than

    (A AND B) OR C

    You need to use parentheses to clarify your logic. Using IN is certainly an option, but we'd need to see the full WHERE clause to see the syntax error.
    Paul

  7. #7
    Join Date
    Feb 2009
    Posts
    28
    Paul thanks for the quick response. I will try that method too. How ever when I run a query with the following criteria
    It still shows these 3 values. I am not sure whats wrong. Technically it shud exclude them right?
    Last edited by kvkanuri; 03-06-09 at 11:17.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You want AND rather than OR. You're saying that if A isn't equal to B OR A isn't equal to C. Whatever ever A might be, logically one of those conditions will always be true and all records will be returned. In other words, if A is equal to B, then while it fails the first test, it meets the second. Since you said OR, then since it meets the second test it is included.
    Paul

  9. #9
    Join Date
    Feb 2009
    Posts
    28
    It didnt work that way either. I am not sure whats wrong. Here is the complete code
    Last edited by kvkanuri; 03-06-09 at 11:17.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Access code formatting is hateful.

    This is more readable IMO and is what your statement currently says:
    Code:
    WHERE        PUBLIC_POSITION.MARKET_SECTOR_DESC NOT IN("Equity", "Derivatives ", "Mtge", "Curncy")
            OR 
                PUBLIC_POSITION.SUB_DESK_CODE NOT IN("CGM_AP_FIN_ACC", "CGM_AU_FIN_ACC", "CITI_AP_FIN_ACC", "CITI_AU_INTERCO_FUND_ACC", "CITI_AU_RISK_TRE AS_ACC", "CITI_AU_SEAGULL_ ACC", "CITI_CA_RISK_TRE AS_ACC", "CITI_FRANK_RISK_ TREAS_ACC", "CITI_HK_RISK_TRE AS_ACC", "CITI_JERSEY_PBG_ ACC", "CITI_JERSEY_RISK _TREAS_ACC", "CITI_JP_RISK_TRE AS_ACC", "CITI_LDN_RISK_TR EAS_ACC", "CITI_NZ_RISK_TRE AS_ACC", "CITI_SG_RISK_TRE AS_ACC", "CITI_US_RISK_TRE AS_RATE_ACC", "CGM_EU_FIN_ACC", "NCLOFF_JP_FIN_AC C", "NCLON_JP_FIN_ACC ")
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2009
    Posts
    28
    Nope still having issues

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by kvkanuri
    Nope still having issues
    My point was, that is exactly the same as your last query just written a bit differently and formatted nicely - it should return exactly the same result. If it had fixed things then I would have been worried!

    However, it is MUCH easier to see exactly what it is doing. So - you say that CGM_AU_FIN_ACC is getting returned right? Of course it is - so long as
    PUBLIC_POSITION.MARKET_SECTOR_DESC is not "Equity" or "Derivatives " or "Mtge" or "Curncy" then your query allows it.

    Perhaps you could explain in English (not SQL) what you want? The problem with posting code and saying "this gives me the wrong result" doesn't help us much as we don't know what the right result would be.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2009
    Posts
    28
    Ahaaa....I could fix my issue. It was just a freakin typo. Anyway thanks for all the help folks....

  14. #14
    Join Date
    Feb 2009
    Posts
    28
    I deleted my code from this page. Thanks once again

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why'd ya do that? You gone all shy?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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