?Issues using DLOOKUP & both negative / positive lookup values?

[Tripp Knightly]

I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to 1000).
When I have a lookup table w/ the "threshold" amounts of income, I'm
not able to get dlookup to work, and I'm pretty sure it's getting
tripped up by negative / positive lookup values. Is it not possible
to do lookups on a table w/ both negatives / positives?

Thanks

[PC Datasheet]

Your first problem is that your bands are not exclusive. A net income that is
between -200 and 0 is also <500. A net income that is <500 but equal to or
greater than 100 is also between 100 and 1000.

Once you solve that problem, create the following table:
TblNetIncomeBands
NetIncomeBandsID
BandText
LowerValue
UpperValue
etc

BandText is <500, -200 to 0, 100 to 1000

To determine which band a customer's net income falls in, create a query that
includes LowerValue and UpperValue. Put the following expression in the criteria
for LowerValue:
<=[CustomerNetIncome]
Put the following expression in the criteria for UpperValue:

=[CustomerNetIncome]

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Tripp Knightly" <tr***********@hotmail.com> wrote in message
news:12*************************@posting.google.co m... I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to 1000).
When I have a lookup table w/ the "threshold" amounts of income, I'm
not able to get dlookup to work, and I'm pretty sure it's getting
tripped up by negative / positive lookup values. Is it not possible
to do lookups on a table w/ both negatives / positives?

Thanks

[Tripp Knightly]

Thank you. That seems sensible.

I will say that my test code on only positive lookup values and a
sorted lookup table didn't seem to raise an exclusivity issue - the
dlookup would "do the right thing" and pick the first qualifying row /
band. You've lost me on "A net income that is <500 but equal to or

greater than 100 is also between 100 and 1000". Something can't be <500 and >= 100. Maybe you meant >500?
Regardless, I can see an alternative approach to try.

I could probably achieve the same just using nested subqueries
(peformance issues or not).

"PC Datasheet" <no****@nospam.spam> wrote in message news:<GB******************@newsread2.news.atl.eart hlink.net>... Your first problem is that your bands are not exclusive. A net income that is
between -200 and 0 is also <500. A net income that is <500 but equal to or
greater than 100 is also between 100 and 1000.

Once you solve that problem, create the following table:
TblNetIncomeBands
NetIncomeBandsID
BandText
LowerValue
UpperValue
etc

BandText is <500, -200 to 0, 100 to 1000

To determine which band a customer's net income falls in, create a query that
includes LowerValue and UpperValue. Put the following expression in the criteria
for LowerValue:
<=[CustomerNetIncome]
Put the following expression in the criteria for UpperValue:

=[CustomerNetIncome]

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Tripp Knightly" <tr***********@hotmail.com> wrote in message
news:12*************************@posting.google.co m...

I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to 1000).
When I have a lookup table w/ the "threshold" amounts of income, I'm
not able to get dlookup to work, and I'm pretty sure it's getting
tripped up by negative / positive lookup values. Is it not possible
to do lookups on a table w/ both negatives / positives?

Thanks

[PC Datasheet]

<< Something can't be <500 and >= 100 >>

100 to 499 all are!

Steve
PC Datasheet
"Tripp Knightly" <tr***********@hotmail.com> wrote in message
news:12**************************@posting.google.c om...

Thank you. That seems sensible.

I will say that my test code on only positive lookup values and a
sorted lookup table didn't seem to raise an exclusivity issue - the
dlookup would "do the right thing" and pick the first qualifying row /
band. You've lost me on "A net income that is <500 but equal to or

greater than 100 is also between 100 and 1000". Something can't be <500 and

= 100. Maybe you meant >500?

Regardless, I can see an alternative approach to try.

I could probably achieve the same just using nested subqueries
(peformance issues or not).

"PC Datasheet" <no****@nospam.spam> wrote in message

news:<GB******************@newsread2.news.atl.eart hlink.net>...

Your first problem is that your bands are not exclusive. A net income that is between -200 and 0 is also <500. A net income that is <500 but equal to or
greater than 100 is also between 100 and 1000.

Once you solve that problem, create the following table:
TblNetIncomeBands
NetIncomeBandsID
BandText
LowerValue
UpperValue
etc

BandText is <500, -200 to 0, 100 to 1000

To determine which band a customer's net income falls in, create a query that includes LowerValue and UpperValue. Put the following expression in the criteria for LowerValue:
<=[CustomerNetIncome]
Put the following expression in the criteria for UpperValue:

=[CustomerNetIncome]

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"Tripp Knightly" <tr***********@hotmail.com> wrote in message
news:12*************************@posting.google.co m...

I have a lookup table from which I want to categorize various bands of
customer net income. Some of the income is positive, some is
negative. The bands vary in size (ie, <500, -200 to 0, 100 to 1000).
When I have a lookup table w/ the "threshold" amounts of income, I'm
not able to get dlookup to work, and I'm pretty sure it's getting
tripped up by negative / positive lookup values. Is it not possible
to do lookups on a table w/ both negatives / positives?

Thanks