Look up table values not in range

[JCAA]

I have a table that look up a percentage factor depending on units used, in this table I have set up values that correspond to the right percentage depending on how many units I have of a specific item. I am not currently getting all my records back as some of my items have a unit of 0 or negative as well as more then 360. I am naturally only getting back my records that are between the numbers in my table. I want to make sure I get the rest of the items the zero or less should have a percentage value of zero and items with excess of 360 units should have a percentage value of 0.95 (95%). I would appreciate any help on this.

Thanks!

 

[TonyJollans]

Hi JCAA,

I suspect there are two things to do - change the JOIN type (from INNER to LEFT?) - and supply default values (e.g. IIf(units<0,0,lookupvalue) - but it's hard to explain in a void. It would be much easier if you could post your Query.

Enjoy,
Tony