Testing value of multiple fields and listing all which =1

[xxx]

I actually have about 13 separate fields in a table I'm trying to summarize in a single report field. Each field corresponds to a particular action taken. If the action was taken the field value is 1, if the action was NOT taken the field value is 0.

The report needs to contain a single field that lists all actions taken. I tried doing this with an If statement formula on the report, but it does not work.

=(IIf([ACTION_VQ_SCAN]=1,"VQ Scan")) And (IIf([ACTION_OTHER]=1,[ACTION_OTHER_SPEC])) And (IIf([ACTION_CULTURE]=1,"Culture(s)")) And (IIf([ACTION_DRUG_THERAPY]=1,"Drug Theraphy"))........ and so on

It looks like it expects all if statements to be true, thus the formula does not return anything. Usually only 1 or 2 actions are actually taken, but I need to account for the possibility of any and all combination of actions.

Does anyone have any suggestions? I'm sure I'm going about it all wrong. Any help appreciated.

 

[plog]

You're going wrong 2 steps before this. Your table is improperly structured. Instead of a field for each action, you need a table to capture the actions. Data should grow vertically (more rows), not horizontally (more columns).

Let's assume your actions are currently in a table called Projects, like this:

Projects
ProjectID, ProjectName, ACTION_VQ_SCAN, ACTION_OTHER, ACTION_CULTURE, ...
17, "Annual Audit", 1, 0, 1, ...

The correct way to have this data is like this:

Projects
ProjectID, ProjectName
17, "Annual Audit"

Actions
ActionID, ProjectID, ActionType
1, 17, ""VO_SCAN"
2, 17, "CULTURE"

For every action related to a project, you store the ProjectID it belongs to and then what action it is in the Actions table. In the above example there is no record for ACTION_OTHER because in the initial data it was 0 meaning it didn't occur.

 

[CJ_London]

The report needs to contain a single field that lists all actions taken

This isn't very clear so not sure I understand but I think what you want is either

concatenate into a string?

[ACTION_VQ_SCAN] & [ACTION_OTHER] & etc

this will give you a binary number type string e.g. 100101000

alternatively if you want the names then modify your code, again to concatenate

=IIf([ACTION_VQ_SCAN]=1,"VQ Scan","") & IIf([ACTION_OTHER]=1,[ACTION_OTHER_SPEC],"") & etc

if I've misunderstood, please provide an example of what you want the field to look like

 

[xxx]

Thank you so much, CJ! Changing "And" to "&" (per your 2nd example) allowed me to do exactly what I wanted.

Also appreciate Plog's response as well. Useful info for future projects.

This isn't very clear so not sure I understand but I think what you want is either

concatenate into a string?

[ACTION_VQ_SCAN] & [ACTION_OTHER] & etc

this will give you a binary number type string e.g. 100101000

alternatively if you want the names then modify your code, again to concatenate

=IIf([ACTION_VQ_SCAN]=1,"VQ Scan","") & IIf([ACTION_OTHER]=1,[ACTION_OTHER_SPEC],"") & etc

if I've misunderstood, please provide an example of what you want the field to look like