I have additional fields in a table where that month may need to increase by a value, 1 field records the month 1-12 and then the other field the amount to increase the monthly figure to.
I am wanting to write and iif statement to say something like this;
if additional_month_1 = 1
Then table1.addional_month_1_amt + query1.month_1
Else if table1.additional_month_2 = 1
then table1.addional_month_2_amt + query1.month_1
Else if table1.additional_month_3 = 1
then table1.addional_month_3_amt + query1.month_1
Else if table1.additional_month_4 = 1
then table1.addional_month_4_amt + query1.month_1
Hope this makes sense and you can point me in the right direction.
IIf is not suited for this kind of operation. You'll end up with a very complex and almost unreadable expression. There probably is a way to solve this kind of problem using "pure" SQL code. Unfortunately, you don't provide any useful piece of information about the data structures that are involved.