Search code examples
ms-access-2016

MS Access -- choose field based on cell contents

Other than building an IIF statement for an Access field in a query, how can I use the cell contents to determine what field I pull data from?

I have a date field and depending on the date I would like to pull data from a different fields (ie: if the date is 1/1/2018 I want to pull from FieldA, if the date is 2/1/2018, I want to pull from FieldB. In the past, I have done that with a long IIF statement, but I wanted to know if there was a more elegant/simpler way.

Solution

  • I'd consider Switch function a bit more elegant.

    Eg.

    SELECT
Switch(
    date < 'xxx', FieldA,
    date >= 'xxx', FieldB
    ) AS field
FROM table;