<< Something can't be <500 and >= 100 >>
100 to 499 all are!
Steve
PC Datasheet
"Tripp Knightly" <trippknightly@hotmail.com> wrote in message
news:120d8f1c.0408030944.27125e1d@posting.google.c om...[color=blue]
> 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[color=green]
> > greater than 100 is also between 100 and 1000". Something can't be <500 and[/color]
>= 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" <nospam@nospam.spam> wrote in message[/color]
news:<GBxPc.23313$iK.20652@newsread2.news.atl.eart hlink.net>...[color=blue][color=green]
> > Your first problem is that your bands are not exclusive. A net income that[/color][/color]
is[color=blue][color=green]
> > 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[/color][/color]
that[color=blue][color=green]
> > includes LowerValue and UpperValue. Put the following expression in the[/color][/color]
criteria[color=blue][color=green]
> > for LowerValue:
> > <=[CustomerNetIncome]
> > Put the following expression in the criteria for UpperValue:[color=darkred]
> > >=[CustomerNetIncome][/color]
> >
> >
> > --
> > PC Datasheet
> > Your Resource For Help With Access, Excel And Word Applications
> >
resource@pcdatasheet.com
> >
www.pcdatasheet.com
> >
> > "Tripp Knightly" <trippknightly@hotmail.com> wrote in message
> > news:120d8f1c.0408021215.f531c26@posting.google.co m...[color=darkred]
> > > 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[/color][/color][/color]
