How do I select a specific column based on a variable in a MS Access query?

Question

I have a large table with the following fields:

Date
Product_ID
AmountEUR_Field1
AmountEUR_Field2
AmountEUR_Field3
AmountEUR_Field4
AmountEUR_Field5

where each AmountEUR field represents the sales amount for a product. The reason for having 5 different AmountEUR fields is that they are based on different Currency Rates (in example BeginingOfMonthRate, AverageMonthRate, EndOfMonthRate etc.).

I now want to copy a specific AmountEUR field to another table, but the AmountEUR field to be copied varies over time (sometimes it is AmountEUR_Field2, other times it is AmountEUR_Field5). Therefore I need to select a specific column based on a variable from another table. (that variable should then have value between 1 and 5).

I have been thinking about making a new field called AmountEUR_ToBeUsed that is updated with the correct AmountEUR_Field, but that brings me back to the same problem of selecting the specific column I want copied.

Can a solution be made within the Access query designer, or do I need some VBA code?

Answer 1

You can just make this with the Access Query designer.

Specifically you will need the function IIF.

For instance, if you want to specify that before a date you wish to use AmountEUR_Field1, and otherwise AmountEUR_Field5 you can say:

IIF(somedate<#1/1/2011#,AmountEUR_Field1,AmountEUR_Field5)

Note, depending on the settings of your PC, you may have to say:

IIF(somedate<#1/1/2011#;AmountEUR_Field1;AmountEUR_Field5)