BETWEEN a rock AND a hard place

hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are
inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the
conditions... 10 = Value = 20). But there are times, especially when
working with intervals, when I might want non-overlapping values like,
say... 10 Value = 20, then 20 Value = 30, etc. This is easy to do
using... WHERE Value 10 And Value = 20 instead of BETWEEN...AND, but it
gets harder when we don't know which of the limits is the lower bound and
which is the upper bound. For example...

SELECT tData.Country
FROM tData
WHERE tData.GDP BETWEEN (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya');

Now all of you gurus probably know which of these limits is the lower one
and which is the upper, but I don't! If I wanted, say, GDP values that
were... lower bound = Value upper bound, I would have to go through
more SQL contortions to get the desired result.

What I'd like to see is an optional phrase that could be attached to the
current BETWEEN...AND operator to make this sort of thing easier, say
something like...

BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit) or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)


Any thoughts? (or am I all wet?)

--
Thanks, cinnie

Using the between it does not matter which is the higher or lower. It works
the same.
For your EXCLUSION use NOT Between.

--
Build a little, test a little.


"cinnie" wrote:

hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are
inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the
conditions... 10 = Value = 20). But there are times, especially when
working with intervals, when I might want non-overlapping values like,
say... 10 Value = 20, then 20 Value = 30, etc. This is easy to do
using... WHERE Value 10 And Value = 20 instead of BETWEEN...AND, but it
gets harder when we don't know which of the limits is the lower bound and
which is the upper bound. For example...

SELECT tData.Country
FROM tData
WHERE tData.GDP BETWEEN (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya');

Now all of you gurus probably know which of these limits is the lower one
and which is the upper, but I don't! If I wanted, say, GDP values that
were... lower bound = Value upper bound, I would have to go through
more SQL contortions to get the desired result.

What I'd like to see is an optional phrase that could be attached to the
current BETWEEN...AND operator to make this sort of thing easier, say
something like...

BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit) or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)


Any thoughts? (or am I all wet?)

--
Thanks, cinnie

cinnie wrote:
hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results
that are inclusive at both ends. (BETWEEN 10 and 20 gives values
meeting the conditions... 10 = Value = 20). But there are
times, especially when working with intervals, when I might want
non-overlapping values like, say... 10 Value = 20, then 20
Value = 30, etc. This is easy to do using... WHERE Value 10
And Value = 20 instead of BETWEEN...AND

It can still be done with BETWEEN. For example, if your data was
Integer:
BETWEEN 11 AND 20

, but it gets harder when we
don't know which of the limits is the lower bound and which is the
upper bound. For example...


And that's only because Access does not force you to put the operands in
the correct order the way other sql dialects do.

BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit)
or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)


Any thoughts? (or am I all wet?)

I guess it would be nice to have, but we don't have the power to make it
happen. It's been quite a while since JetSQL was updated, but, you could
always try submitting this request to MS. I suspect they would reject
the request as it's not per SQL standards.

As to your problem, I would use VBA to solve it.
Assign to the GDP from both countries to variables, then assign the
lower of the two to a variable called lowlimit and the higher to a
variable called highlimit, then pass those as parameters to your sql
statement, increasing the value of the low limit if you want it excluded
or vice versa with the high limit.




--
HTH,
Bob Barrows

When you want to use ( and ) or (= and =) but you don't know which one
is lower or greater, the two possible SQL contortions that I see would be
first to simply to use *both* cases:

WHERE (tData.GDP (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND tData.GDP (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya')
)

OR (tData.GDP (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya')
AND tData.GDP (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
)

The 'wrong' case will return no result; so you get what you want. However,
a better solution would be to use the Min() and Max() functions to retrieve
the required result for both subqueries (untested):

WHERE tData.GDP (SELECT Min (tData.GDP)
FROM tData
WHERE tData.Country in ('Nigeria',
'Kenya'))
AND tData.GDP (SELECT Max (tData.GDP)
FROM tData
WHERE tData.Country in ('Nigeria',
'Kenya'))


This way, you see that you don't really need to use an optional phrase or
keyword.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"cinnie" wrote in message
...
hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that
are
inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the
conditions... 10 = Value = 20). But there are times, especially
when
working with intervals, when I might want non-overlapping values like,
say... 10 Value = 20, then 20 Value = 30, etc. This is easy to
do
using... WHERE Value 10 And Value = 20 instead of BETWEEN...AND, but
it
gets harder when we don't know which of the limits is the lower bound and
which is the upper bound. For example...

SELECT tData.Country
FROM tData
WHERE tData.GDP BETWEEN (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya');

Now all of you gurus probably know which of these limits is the lower one
and which is the upper, but I don't! If I wanted, say, GDP values that
were... lower bound = Value upper bound, I would have to go through
more SQL contortions to get the desired result.

What I'd like to see is an optional phrase that could be attached to the
current BETWEEN...AND operator to make this sort of thing easier, say
something like...

BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit) or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)


Any thoughts? (or am I all wet?)

--
Thanks, cinnie

Cinnie:

I think you can probably also do it without subqueries:

SELECT T1.Country, T1.GDP
FROM TData As T1, TData As T2
WHERE T2.Country IN('Nigeria', 'Kenya')
GROUP BY T1.Country, T1.GDP
HAVING MAX(T2.GDP) MIN(T1.GDP)
AND MIN(T2.GDP) MAX(T1.GDP);

Ken Sheridan
Stafford, England

cinnie wrote:
hello gurus

This isn't a question as much as it is a beginner's comment on
BETWEEN...AND. As it seems to be used, BETWEEN...AND gives results that are
inclusive at both ends. (BETWEEN 10 and 20 gives values meeting the
conditions... 10 = Value = 20). But there are times, especially when
working with intervals, when I might want non-overlapping values like,
say... 10 Value = 20, then 20 Value = 30, etc. This is easy to do
using... WHERE Value 10 And Value = 20 instead of BETWEEN...AND, but it
gets harder when we don't know which of the limits is the lower bound and
which is the upper bound. For example...

SELECT tData.Country
FROM tData
WHERE tData.GDP BETWEEN (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Nigeria')
AND (SELECT tData.GDP
FROM tData
WHERE tData.Country = 'Kenya');

Now all of you gurus probably know which of these limits is the lower one
and which is the upper, but I don't! If I wanted, say, GDP values that
were... lower bound = Value upper bound, I would have to go through
more SQL contortions to get the desired result.

What I'd like to see is an optional phrase that could be attached to the
current BETWEEN...AND operator to make this sort of thing easier, say
something like...

BETWEEN ... AND ... IncExc
(for including the lower limit but excluding the upper limit) or...

BETWEEN ... AND ... ExcExc
(for excluding the both limits) or...

BETWEEN ... AND
(which defaults to the currently used IncInc)

Any thoughts? (or am I all wet?)


--
Message posted via http://www.accessmonster.com

I'd got into a MIN/MAX mindset! On second thoughts, even simpler:

SELECT T1.Country, T1.GDP
FROM TData As T1, TData As T2
WHERE T2.Country IN('Nigeria', 'Kenya')
GROUP BY T1.Country, T1.GDP
HAVING MAX(T2.GDP) T1.GDP
AND MIN(T2.GDP) T1.GDP;

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com