How To Create A Field Name Dependent On Multiple Fields

[dskysmine]

Hello all,

I have a Table with multiple fields, and I am trying to create a single field that is the concatenation of fields that have data on them.
Check Attachment how the table is.

I know that Field A is always present, but in the others there might be data or it might be "NA".
What I want to create is a Query that returns in one field the concatenation of the fields that have data.
So for Record 1 it would be: A1_B2_D1_E1
For record 2: A2_C2_D2
For record 3: A3_B3_C3_D3_F3

I tried doing this using IIF and Switch in Access, but it doesn't work.
This kind of logic doesn't work in Access:
Field1: [A] &"_"& IIF([B]="NA","",[B]) & ...
And with nested IIFs (from [A] to [F]) if there is a value in F it will return for record 3: A3_B3_C3_D3_NA_F3

Does anybody know how to do this?
Thank you very much.
Kind regards.

PS: My access is 2007.

[JoeM]

I think you were on the right track. Making a minor tweak to your previous attempt should work:

Field1: [A] & IIf([B]="NA","","_" & [B]) & IIf([C]="NA","","_" & [C]) & IIf([D]="NA","","_" & [D]) & IIf([E]="NA","","_" & [E]) & IIf([F]="NA","","_" & [F])

[dskysmine]

You are right. It worked great. Thank you very much!