Selecting a record on a value being between two fields in that record

[simon_marklar]

Hi Guys,

Im trying to do something that i dont think is possible, but perhaps i dont know the correct command.

I have a table, which contains costs. the costs are deceided by a circulation. if the circulation is below 10,000, then the cost is A. if its more than 10,000 but less than 20,000, the cost is B.

I have a query that brings back the titles, and thier circulations. what i want to do is use this data to lookup the correct cost. is this poossible?

thanks
%simon

 

[reclusivemonkey]

Have a look in the help file for "IIF", thats exactly what you need here. You'll find "IIF" *very* useful.

 

[M8KWR]

you can use a iif inside another iif statement, i think that should solve it, but i could do with know more information, table and field names etc so i can write it for you if yo have not used iif statements before.

 

[simon_marklar]

thanks. worked like a charm. used IIF like this:

CostIndex: IIf([MaxOfTotal]<20000,0,IIf([MaxOfTotal]<50000,1,IIF([MaxOfTotal]<100000,2,IIF([MaxOfTotal]<250000,3,4))))

to get the numerical value for the corresponding value in the costs table, then use another query to get the rest of the info needed.

Thanks!