Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Ignoring Criteria (2002 SP-2)

    Hello,

    I'm getting frustrated with a fairly simple query that won't cooperate! The Zip field (text format) has some records with data and some without. But when I try to set the criteria to pick one or the other (Is Null or Is Not Null) the query just ignores my specification and I get everything.

    I'm sure I'd have more patience if I didn't have to get this done before I go on vacation tomorrow. Any ideas?

    Thanks!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Ignoring Criteria (2002 SP-2)

    Can you post your query?
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Ignoring Criteria (2002 SP-2)

    SELECT "5" AS RecTyp, Format("000000000") AS TRNum, Format(DepSetUp!ProfCtr,"00000") AS Branch, Format(DepSetUp!RefNr,"00000000000000000000") AS AcctNr, " " AS PdAt, "000101" AS STN, "000050" AS Quan, " " AS Sty, "000000" AS DepQnt, [Branch] AS BlPrCt, "09" AS AcLn, IIf(IsNull([CustomerName1]),"A","B") AS MTC, " " AS MccDt, zCusNAStructured.NameLine1, zCusNAStructured.NameLine2, zCusNAStructured.NameLine3, zCusNAStructured.StreetLine1, " " AS Phone, zCusNAStructured.StreetLine2, CusPostl.City, CusPostl.State, CusPostl.Zip, zAltNAGrouped.CustomerName1, zAltNAGrouped.CustomerName2, zAltNAGrouped.CustomerName3, zAltNAGrouped.CustomerName4, zAltNAGrouped.CustomerName5, zAltNAGrouped.CustomerName6, " " AS Res
    FROM ((DepSetUp LEFT JOIN zCusNAStructured ON DepSetUp.CusNr = zCusNAStructured.CusNr) LEFT JOIN CusPostl ON DepSetUp.CusNr = CusPostl.CusNr) LEFT JOIN zAltNAGrouped ON (DepSetUp.AI = zAltNAGrouped.AI) AND (DepSetUp.CusNr = zAltNAGrouped.CusNr)
    WHERE (((CusPostl.Zip) Is Not Null) AND ((DepSetUp.Cl)=2) AND ((DepSetUp.St)<>6 And (DepSetUp.St)<>7 And (DepSetUp.St)<>9)) OR (((DepSetUp.Cl)=3)) OR (((DepSetUp.Cl)=4)) OR (((DepSetUp.Cl)=6)) OR (((DepSetUp.Cl)=7)) OR (((DepSetUp.Cl)=11)) OR (((DepSetUp.Cl)=12)) OR (((DepSetUp.Cl)=14 Or (DepSetUp.Cl)=18 Or (DepSetUp.Cl)=19 Or (DepSetUp.Cl)=20));


    Fun, huh?

    Now, I checked the setup on the table CusPostl that has the Zip field. It is formatted text, as I said. The only thing I can think of is that there would be another designation for a blank field besides "null" to eliminate...

    Thanks for taking a look at this!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Ignoring Criteria (2002 SP-2)

    I have attached a screenshot of the criteria for your query. Here is the WHERE part without the myriad superfluous parentheses Access adds:

    WHERE (CusPostl.Zip Is Not Null AND DepSetUp.Cl=2 AND DepSetUp.St<>6 And DepSetUp.St<>7 And DepSetUp.St<>9) OR DepSetUp.Cl=3 OR DepSetUp.Cl=4 OR DepSetUp.Cl=6 OR DepSetUp.Cl=7 OR DepSetUp.Cl=11 OR DepSetUp.Cl=12 OR (DepSetUp.Cl=14 Or DepSetUp.Cl=18 Or DepSetUp.Cl=19 Or DepSetUp.Cl=20)

    I doubt very much that this is what you intend. The condition Zip Is Nt Null is only combined with DepSetUp.Cl=2 AND DepSetUp.St<>6 And DepSetUp.St<>7 And DepSetUp.St<>9, not with any of the other conditions. If you want to combine with the others, you must repeat Is Not Null in each criteria row. The same may hold for <>6 And <>7 And <>9.
    Attached Images Attached Images
    • File Type: png x.png (2.9 KB, 0 views)

  5. #5
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Ignoring Criteria (2002 SP-2)

    Y'kno what - you're absolutely right! I never knew that the lines corresponded. That just makes too much sense ;-]

    Thanks for kicking me out of my stupor! I think I'll be able to get it now.

    You guys ROCK!

  6. #6
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Ignoring Criteria (2002 SP-2)

    Final update - I include all the criteria on the first line, and it works like a charm. I see what I was doing wrong. 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
  •