Return a field name from function

[boycie]

Hi everyone, hopefully someone can help me out this evening. I need to be able to return a field name to use in a query;
I have query1 which returns a code eg.either 1001,1002,1003 etc and other data including price1,price2,price3,price4

I have a table holding info about the code;

1001,PriceToUse (which will be a field name eg. price1 or price2 or price3 or price4)

So, if the code returned in query1 is 1001, then price1 should be used in column in query1 or if 1002 is returned, then price 2 is to be used.ie. 5 * [Price1] or 5 * [Price2]

I have got it working using a multiple iif but thought this should really be a function.

ps. I would have designed the tables differently but I have whats given me unfortunately.

Hope I've explained this ok. Thanks for any help

Ian

 

[gemma-the-husky]

just use two dlookups - read the 1001 value - then based on that, do another lookup to read the final value

 

[boycie]

Thanks for the reply. I dont think I've explained it very well. I have attached a mock db I quickly made. If you look at query1, I need a calculated field that will use the relevant field eg. row 1 of the query will require 4 * 12.50 (price1), row 2:4*11.00 (price1),row3:8*7.50(price2) etc.

 

[boycie]

sorry, db now attached hopefully