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

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

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?

Solution

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)