Returning an answer based upon a range..

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
 
J

John Vinson

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]
 
A

andymqld

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

Regards,

andymqld
 
J

John Vinson

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]
 
Top