Solved

using two ANDs in a Query

Posted on 2011-09-29
15
263 Views
Last Modified: 2012-05-12
Hi all,

I would like to specify multiple query using two ANDs using Select Query. I am basing a report which has to pick two ranges within the same field, ie.

range from 60 to 69 and from 90 to 99.

I have tried the following in the criteria box.

(between 60 AND 69) and (between 90 AND 99)....no luck

I have tried permutations of the above but to no avail.

Grateful to know if it can be done?

Thanks
0
Comment
Question by:PipMic
  • 7
  • 3
  • 3
  • +1
15 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 36813675
It not AND but OR to use.

/gustav
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36813676
I am assuming that the field value that has to be in the range cannot be in 60 to 69 and 90 to 99 as it would be a discrete value - either 60 to 69 or 90 to 99, so the where clause would be BETWEEN 60 an 69 or BEWTWEEN 60 and 99

Kelvin

0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36813679
That is something like WHERE ID Between 60 and 69 or ID between 90 and 99

Kelvin
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:PipMic
ID: 36813696
Hi guys,

I would like my query to pick "both ranges", ie, the values pertaining to ID from 60 to 69 "and" the values pertaining to the range 90 to 99.
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36813706
Can you give an example of the sort of data you are talking about?
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 58 total points
ID: 36813741
> I would like my query to pick "both ranges" ..

That's what have been written:

(between 60 AND 69) OR (between 90 AND 99)....

/gustav
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36813756
Another way, but a bit heavy going would be where ID IN(60,61,62,63,64,65,66,67,68,69,90,91,92,93,94,95,96,97,98,99)
0
 
LVL 5

Assisted Solution

by:DerZauberer
DerZauberer earned 44 total points
ID: 36813771
SELECT field1, field2, ... FROM table WHERE (field BETWEEN 60 AND 69) OR (field BETWEEN 90 AND 99)

Open in new window

0
 

Author Comment

by:PipMic
ID: 36813801
Hi,

I have a field ID as autonumber.

But I would like to select two ranges from the same field eg. Simply put...

1 - A
2 - A
3 - B
4 - C
5 - C

Therefore I would like to select the values pertaining to two ranges i.e. from 1 to 2 and from 4 to 5.

Thanks

0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 23 total points
ID: 36813826
SELECT Other field from thetable
WHERE ID BETWEEN 1 and 2 or ID Between 4 and 5
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36813831
Will return
A
A
C
C
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 58 total points
ID: 36813834
That would be:

(between 1 AND 2) OR (between 4 AND 5)

/gustav

0
 
LVL 5

Assisted Solution

by:DerZauberer
DerZauberer earned 44 total points
ID: 36813845
In your example your filter MUST be like this:

Filter: (ID between 1 and 2) OR (ID between 4 and 5)

If you use "AND" it has to meet both criterias at same time, which can never be true!
0
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36813853
I have successfully used these expressions without bracketing for the last 15 years1 The query wizards add them, and unless you need toem to determine a order of operations they can be left off - and often result in increased performance.

But this is only detracting from the question.
0
 

Author Closing Comment

by:PipMic
ID: 36813921
worked well
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question