Returning an answer based upon a range..

  • Thread starter Thread starter apmeehan
  • Start date Start date
A

apmeehan

Dear All,

I've tried to research this question, but maybe my terminology is
wrong.... but basically this is what I am after...

I have a table which includes a field containing a percentage. I which
to convert this percentage to a value based upon a pre-determined
range..

eg... a percentage of between 0% and 5% gives a value of 10, a
percentage of between 6% and 10% gives a value of 9 .... a percentage
of 60% and 75% gives a value of 3.

As usual, I can do this perfectly with a VLOOKUP in Excel, but how do I
manage this in Access? (there are more than 7 possibilities, so I
presume the IIF statement is out of the question...).

Many thanks,

andymqld
 
Dear All,

I've tried to research this question, but maybe my terminology is
wrong.... but basically this is what I am after...

I have a table which includes a field containing a percentage. I which
to convert this percentage to a value based upon a pre-determined
range..

eg... a percentage of between 0% and 5% gives a value of 10, a
percentage of between 6% and 10% gives a value of 9 .... a percentage
of 60% and 75% gives a value of 3.

As usual, I can do this perfectly with a VLOOKUP in Excel, but how do I
manage this in Access? (there are more than 7 possibilities, so I
presume the IIF statement is out of the question...).
Click to expand...

Try using the Switch() function. It takes arguments in pairs; if the
first member of a pair evaluates to TRUE, the function returns the
second member of that pair and quits. E.g.

Switch([pct] < 0.05, 10, [pct] < 0.1, 9, <etc>, [pct] < 0.75, 3, True,
999)

This will return 999 if the value is nowhere in the range 0 to .75.

John W. Vinson[MVP]
 
Thanks John ... is there a restricted number of iterations to be used?

Regards,

andymqld
 
Thanks John ... is there a restricted number of iterations to be used?
Click to expand...

I've never run into a limit; I suspect that the limit on (IIRC) 1024
bytes in a single expression will be the first to hit.

If you need MANY ranges, a table of ranges with fields Low, High and
RangeName can be used; use a "non equi join", with a Join clause like

INNER JOIN Ranges ON yourtable.value >= Ranges.Low AND yourtable.value
< Ranges.High

with careful attention to the >=, >, < and <= operators to match the
range values you've chosen.

John W. Vinson[MVP]
 
Back
Top