IIf statement too complex

[Andy G]

Is there a diffferent (better) way to do the following. I have a table
where the user can enter information in any number of 15 user defined text
boxes. I am trying to display all fields they've typed in these user
defined text boxes in a report. So if the user typed in 'Yes' in Box1,
'Maybe' in Box 6, 'No' in Box 10 and nothing else in any of the other boxes
the text box in the report should read 'Yes Maybe No'. With the below
control source it will just get the first value and that's it.

The control source in the text box would be something along these lines:

= IIf(Not IsNull([USER_VARBL1_DATA]),Trim([USER_VARBL1_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL2_DATA]),Trim([USER_VARBL2_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL3_DATA]),Trim([USER_VARBL3_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL4_DATA]),Trim([USER_VARBL4_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL5_DATA]),Trim([USER_VARBL5_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL6_DATA]),Trim([USER_VARBL6_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL7_DATA]),Trim([USER_VARBL7_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL8_DATA]),Trim([USER_VARBL8_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL9_DATA]),Trim([USER_VARBL9_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL10_DATA]),Trim([USER_VARBL10_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL11_DATA]),Trim([USER_VARBL11_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL12_DATA]),Trim([USER_VARBL12_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL13_DATA]),Trim([USER_VARBL13_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL14_DATA]),Trim([USER_VARBL14_DATA] &" ") & _
IIf(Not IsNull([USER_VARBL15_DATA]),Trim([USER_VARBL15_DATA]))

 

[Marshall Barton]

Is there a diffferent (better) way to do the following. I have a table
where the user can enter information in any number of 15 user defined text
boxes. I am trying to display all fields they've typed in these user
defined text boxes in a report. So if the user typed in 'Yes' in Box1,
'Maybe' in Box 6, 'No' in Box 10 and nothing else in any of the other boxes
the text box in the report should read 'Yes Maybe No'. With the below
control source it will just get the first value and that's it.

The control source in the text box would be something along these lines:

= IIf(Not IsNull([USER_VARBL1_DATA]),Trim([USER_VARBL1_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL2_DATA]),Trim([USER_VARBL2_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL3_DATA]),Trim([USER_VARBL3_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL4_DATA]),Trim([USER_VARBL4_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL5_DATA]),Trim([USER_VARBL5_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL6_DATA]),Trim([USER_VARBL6_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL7_DATA]),Trim([USER_VARBL7_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL8_DATA]),Trim([USER_VARBL8_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL9_DATA]),Trim([USER_VARBL9_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL10_DATA]),Trim([USER_VARBL10_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL11_DATA]),Trim([USER_VARBL11_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL12_DATA]),Trim([USER_VARBL12_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL13_DATA]),Trim([USER_VARBL13_DATA] & " ") & _
IIf(Not IsNull([USER_VARBL14_DATA]),Trim([USER_VARBL14_DATA] &" ") & _
IIf(Not IsNull([USER_VARBL15_DATA]),Trim([USER_VARBL15_DATA]))

Try this kind of expression:

=([USER_VARBL1_DATA] + " ") & ([USER_VARBL2_DATA] + " ") &
([USER_VARBL3_DATA] + " ") & ([USER_VARBL4_DATA] + " ") &
. . .

Do not use the _ in a control source expression.