Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    MS Query (Excel 2002)

    Hi,
    I have a query with a criteria value as follows:

    Between '8000000' And '8000499'

    I need it to also exclude '8000155', however when I had the criteria value of <>'8000155' underneath the between values seen above, the query returns data that isn't even close to what was requested. I do have 3 other criterias, and did not enter anything on their second lines for values. I tried to repeat the first value on the 2nd value lines for these other 3 criteria, and then I got massive amounts of data, which was wrong again. Any ideas on how I can get the query to return both the between AND the exclusion of the one account?
    Thanks!!
    Lana

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

    Re: MS Query (Excel 2002)

    If you put criteria on two different lines, you specify that records must satisfy one OR the other (or both). You want records to satisfy one AND the other. For this, you must put the criteria on one line:

    Between '8000000' And '8000499' AND <>'8000155'

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002)

    This makes sense... and of course I got it to work with your guidance... I was over complicating it!
    Thanks so much Hans!
    Lana

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002)

    I have another related question... now that I've got the one account EXCLUDED, I need to add another account that is NOT in the range of 8000000 thru 8000499 - it is 9955000. I tried to add it to the line as follows, and it didn't work.
    Between '8000000' And '8000499' And <>'8000155' And <>'8000055' And ='9955000'

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002)

    The last condition needs to be an "Or", otherwise you are looking for a single account that is between '8000000' And '8000499' and also equal to 9955000

    Between '8000000' And '8000499' And <>'8000155' And <>'8000055' <font color=red>Or</font color=red> ='9955000'

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

    Re: MS Query (Excel 2002)

    Alternatively, you could put '9955000' on the second criteria line. As I tried to explain in my previous reply, that means the two criteria are connected by OR.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: MS Query (Excel 2002)

    This works perfect... thanks for the lesson Hans!
    Lana

Posting Permissions

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