467,166 Members | 1,002 Online

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

 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 Nov 13 '05 #1
• viewed: 2357
Share:
3 Replies
 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" 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 Nov 13 '05 #2
 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" wrote in message news:... 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" 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 Nov 13 '05 #3
 << Something can't be <500 and >= 100 >> 100 to 499 all are! Steve PC Datasheet "Tripp Knightly" 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" wrote in message news:... 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" 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 Nov 13 '05 #4

### This discussion thread is closed

Replies have been disabled for this discussion.