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