Filter using 2 separate ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to run a query that shows specific store numbers. Each property
has multiple stores and I'm creating a range filter for each property, but I
have one property that has a range from 102-154 and I want to exclude stores
122-123. Here's my sql:

SELECT tblMarkdowns.STORE, tblMarkdowns.SKU, tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR, tblMarkdowns.[OLD PRICE], tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<="119" And
(tblMarkdowns.STORE)>="130" And (tblMarkdowns.STORE)<="154"))
ORDER BY tblMarkdowns.STORE;

Please help...
Thank you.
 
Why not...


WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<"122" OR
(tblMarkdowns.STORE)>="124" And (tblMarkdowns.STORE)<="154")) ORDER BY
tblMarkdowns.STORE;


Between 102 and 122 OR Between 124 and 154
 
SELECT tblMarkdowns.STORE,
tblMarkdowns.SKU,
tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR,
tblMarkdowns.[OLD PRICE],
tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE tblMarkdowns.STORE Between "102" And "154"
and tblMarkdowns.STORE Not In ("122", "122")
ORDER BY tblMarkdowns.STORE;

Now there is the little problem of your store numbers being text. Things
could get thrown out of wack if you have any 1, 2, or 4 and more character
stores like "12" or "1022". If that happens, something like below might work.

WHERE CInt(tblMarkdowns.STORE) Between 102 And 154
and tblMarkdowns.STORE Not In ("122", "122")
 
Wouldn't this only run either below "122" or above "123"? I need to show both.

Rick B said:
Why not...


WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<"122" OR
(tblMarkdowns.STORE)>="124" And (tblMarkdowns.STORE)<="154")) ORDER BY
tblMarkdowns.STORE;


Between 102 and 122 OR Between 124 and 154





--
Rick B



JonQ said:
I'm trying to run a query that shows specific store numbers. Each property
has multiple stores and I'm creating a range filter for each property, but
I
have one property that has a range from 102-154 and I want to exclude
stores
122-123. Here's my sql:

SELECT tblMarkdowns.STORE, tblMarkdowns.SKU, tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR, tblMarkdowns.[OLD PRICE], tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<="119" And
(tblMarkdowns.STORE)>="130" And (tblMarkdowns.STORE)<="154"))
ORDER BY tblMarkdowns.STORE;

Please help...
Thank you.
Click to expand...
Click to expand...
 
Thanks everyone. I couldn't find the right code to put in between and
excluding. Code worked perfectly.

Jerry Whittle said:
SELECT tblMarkdowns.STORE,
tblMarkdowns.SKU,
tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR,
tblMarkdowns.[OLD PRICE],
tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE tblMarkdowns.STORE Between "102" And "154"
and tblMarkdowns.STORE Not In ("122", "122")
ORDER BY tblMarkdowns.STORE;

Now there is the little problem of your store numbers being text. Things
could get thrown out of wack if you have any 1, 2, or 4 and more character
stores like "12" or "1022". If that happens, something like below might work.

WHERE CInt(tblMarkdowns.STORE) Between 102 And 154
and tblMarkdowns.STORE Not In ("122", "122")

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


JonQ said:
I'm trying to run a query that shows specific store numbers. Each property
has multiple stores and I'm creating a range filter for each property, but I
have one property that has a range from 102-154 and I want to exclude stores
122-123. Here's my sql:

SELECT tblMarkdowns.STORE, tblMarkdowns.SKU, tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR, tblMarkdowns.[OLD PRICE], tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<="119" And
(tblMarkdowns.STORE)>="130" And (tblMarkdowns.STORE)<="154"))
ORDER BY tblMarkdowns.STORE;

Please help...
Thank you.
Click to expand...
Click to expand...
 
Whoops! Typo on my part. One of the 122's should be 123.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


JonQ said:
Thanks everyone. I couldn't find the right code to put in between and
excluding. Code worked perfectly.

Jerry Whittle said:
SELECT tblMarkdowns.STORE,
tblMarkdowns.SKU,
tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR,
tblMarkdowns.[OLD PRICE],
tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE tblMarkdowns.STORE Between "102" And "154"
and tblMarkdowns.STORE Not In ("122", "122")
ORDER BY tblMarkdowns.STORE;

Now there is the little problem of your store numbers being text. Things
could get thrown out of wack if you have any 1, 2, or 4 and more character
stores like "12" or "1022". If that happens, something like below might work.

WHERE CInt(tblMarkdowns.STORE) Between 102 And 154
and tblMarkdowns.STORE Not In ("122", "122")

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


JonQ said:
I'm trying to run a query that shows specific store numbers. Each property
has multiple stores and I'm creating a range filter for each property, but I
have one property that has a range from 102-154 and I want to exclude stores
122-123. Here's my sql:

SELECT tblMarkdowns.STORE, tblMarkdowns.SKU, tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR, tblMarkdowns.[OLD PRICE], tblMarkdowns.[NEW PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<="119" And
(tblMarkdowns.STORE)>="130" And (tblMarkdowns.STORE)<="154"))
ORDER BY tblMarkdowns.STORE;

Please help...
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
no, it finds all records where either of those conditions is true. Each
record only has ONE value. If the value is between 102 and 122 or if it is
between 124 and 154, then that record will be selected.
--
Rick B



JonQ said:
Wouldn't this only run either below "122" or above "123"? I need to show
both.

Rick B said:
Why not...


WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<"122" OR
(tblMarkdowns.STORE)>="124" And (tblMarkdowns.STORE)<="154")) ORDER BY
tblMarkdowns.STORE;


Between 102 and 122 OR Between 124 and 154





--
Rick B



JonQ said:
I'm trying to run a query that shows specific store numbers. Each
property
has multiple stores and I'm creating a range filter for each property,
but
I
have one property that has a range from 102-154 and I want to exclude
stores
122-123. Here's my sql:

SELECT tblMarkdowns.STORE, tblMarkdowns.SKU, tblMarkdowns.DESCRIPTION,
tblMarkdowns.VENDOR, tblMarkdowns.[OLD PRICE], tblMarkdowns.[NEW
PRICE],
tblMarkdowns.[ON HAND]
FROM tblMarkdowns
WHERE (((tblMarkdowns.STORE)>="102" And (tblMarkdowns.STORE)<="119" And
(tblMarkdowns.STORE)>="130" And (tblMarkdowns.STORE)<="154"))
ORDER BY tblMarkdowns.STORE;

Please help...
Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top