Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Feb 2004
    Posts
    43

    Unanswered: Unexpected Results in Query

    I have this code for a query:-

    SELECT lates.*
    FROM lates
    WHERE lates.Date=[Enter Date] AND lates.Customer="Kitchen Range" OR lates.Customer_2="Kitchen range";


    Surely i should only return results with "Kitchen Range" as the customer?

    Obviously im doing so0mething wrong-but what?

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    What are you getting?

    S-

  3. #3
    Join Date
    Feb 2004
    Posts
    142
    You need to encapsulate the 'OR' section to run as a single test, note the use of "()".

    WHERE lates.Date=[Enter Date] AND (lates.Customer="Kitchen Range" OR lates.Customer_2="Kitchen range");

    This will return all lates.date with the user supplied param. that have 'Kitchen range' in either the customer or the customer_2 field.
    KC

  4. #4
    Join Date
    Feb 2004
    Posts
    43
    Many Thanks but.........

    I have tried again with no luck-i am still getting some reults which arent "Kitchen Range" - so i must still be doing something wrong?

  5. #5
    Join Date
    Nov 2003
    Posts
    267
    Can you post a sample of you results, specifically

    lates.Date, lates.Customer, lates.Customer_2

    S-

  6. #6
    Join Date
    Feb 2004
    Posts
    43
    I am querying 18 Fields named 1 to 18 - Now i should only be returning any of those fields with Kitchen range in the named cells - Unfortunately i am still returning some other suppliers in the fields 1 to 18.

    I just cant understand why -as when i only query Customer_1 for "Kitchen Range" the query works perfectly.

  7. #7
    Join Date
    Nov 2003
    Posts
    267
    Since you won't post results data and based on what you are says. It sound like you are getting exactly what you are requesting

    You results is probably coming like the following

    Date | Customer | Customer_2
    11/15/04 | Kitchen Range | Kitchen Range
    11/15/04 | Fridge | Kitchen Range
    11/15/04 | Kitchen Range | Fridge


    That is what you are requesting. Which is why when you run it for one of the customer you get what you want.

    Now to get help, and to not fustrate yourself or us (who are trying to help you) post some sample data for post what you want the results to look like based on the sample data you posted.

    S-

  8. #8
    Join Date
    Feb 2004
    Posts
    43
    There are 35 fields buddy-i am not trying to mislead you at all-i am just not sure how i would post 35 fields as results-thats all.

    Its not laziness-merely a lack of understanding.

  9. #9
    Join Date
    Feb 2004
    Posts
    142
    Sometimes with the jet it will reparse a query differently than intended by the developer if it is done in the QBE window. This usually only happens with complex criteria.

    If you are using pure SQL then the example I gave you earlier will yield the result set posted above. If you are getting results that include something other than kitchen range in the customer and customer_2 field then check the basics. Is this the only criteria being used and if so, is it all on the same line in the QBE window?

    If you are using the QBE window, things on the same line are ANDs and things on different lines are ORs.
    KC

  10. #10
    Join Date
    Nov 2003
    Posts
    267
    Just post the three fields that are part of the criteria your query (like I did eariler)

    Show some examples of what your data looks like

    And show what you want the query to bring back.

    I'm not saying your lasy, but if you really want help, you have to provide what we need to help you, in your case it is sample data. You can also post you Dataset in mdb or xls (within a zipped file) through the use of attachements.

    S-

  11. #11
    Join Date
    Feb 2004
    Posts
    43
    Now attached.
    Attached Files Attached Files

  12. #12
    Join Date
    Nov 2003
    Posts
    267
    Got your data.

    It is as I suspected, the query is bring back what you have requested (based on your first SQL statement posted)

    I believe that you have written you statement wrong (as you don't like what you are getting)

    based on the data in the xls file, what do you want the query to bring back to you.

    S-

  13. #13
    Join Date
    Feb 2004
    Posts
    43
    Thanks for looking,

    In the customer 1 to 18 fields i want to only see"kitchen Range" i dont want to see any other results in there(i will write the queries for the other makers after i get the model right for this one)

    Does that answer make sense?

  14. #14
    Join Date
    Nov 2003
    Posts
    267
    So what you are after is all records, all columns, where kitchen Range exisits in one of the Customer columns?

    S-

  15. #15
    Join Date
    Feb 2004
    Posts
    43
    Yes thats about it.

Posting Permissions

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