IIF/SWITCH in text box

• smallmoney

  SSCarpal Tunnel

  Points: 4378

  More actions

  September 14, 2009 at 12:44 pm

  #204676

  Hi Guys,

  I'm trying to get a text box to print address lines.

  There are three address lines and I want it to:

  1. Only print address line 1 if line 2 and 3 are blank

  2. Only print address line 1 and 2 if 3 is blank

  3. Print all lines if none are blank

  I want to implement this logic because if I use:

  =Fields!address1_line1.Value + chr(13) + chr(10) + Fields!address1_line2.Value + chr(13) + chr(10) + Fields!address1_line3.Value

  Then it will add blank lines when line 2 and 3 are blank.

  So it would look like

  Name

  Line1

  city, state, zip

  I attempted these but they don't work

  =SWITCH(Fields!address1_line2.Value = "" AND Fields!address1_line3.Value = "", Fields!address1_line1.Value,

  Fields!address1_line3.Value = "" AND Fields!address1_line2.Value <> "", Fields!address1_line1.Value + chr(13) + chr(10) + Fields!address1_line2.Value,

  Fields!address1_line1.Value + chr(13) + chr(10) + Fields!address1_line2.Value + chr(13) + chr(10) + Fields!address1_line3.Value)

  =IIF(Fields!address1_line2.Value <> "" Fields!address1_line3.Value <> "", Fields!address1_line1.Value + chr(13) + chr(10) + Fields!address1_line2.Value + chr(13) + chr(10) + Fields!address1_line3.Value)

  IIF(Fields!address1_line2.Value <> "" Fields!address1_line3.Value = "", Fields!address1_line1.Value + chr(13) + chr(10) + Fields!address1_line2.Value)

  IIF(Fields!address1_line2.Value = "" AND Fields!address1_line3.value = "", Fields!address1_line1.Value)

  THANKS!

• boumerlin

  SSCrazy

  Points: 2023

  More actions

  September 15, 2009 at 5:45 pm

  #1054191

  I've done this, but truly I find it much easier to drop this logic into my stored procedure. You can build an Address string in there and use char(13) and char(10) for your Carriage Return/Line Feeds. Debugging in a sproc is 99 times easier than trying to debug in the BIDS interface.