IIf statement too complex

  • Thread starter Thread starter Andy G
  • Start date Start date
A

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]))
 
Andy said:
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]))
Click to expand...


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.
 
Back
Top