Hi James,

I think that the lengh of the string created by your IIF statement exceeds 254 character limit on the field lenght. You sould always use PADR() function on character expressions in select to make sure that they are of proper lenght. You can also simplify you expression as follows

SELECT PADR(trim(name) + " " +  ;
			IIF( NOT EMPTY(location), "(" + trim(location) + ") ", " ") + ;
			trim(detail) + ;
			IIF( NOT EMPTY(rx), " Rx- " + trim(rx), ""), 254) as mdetail ;
		from hshealth

>Hi All,
>
>I'd like to use a multiple iif in a select statement but am getting a error message "String is too long to fit".
>
>The below sample shows the syntax.
>
>If I can get it to work, I will be adding a few more fields to the select, but I'm starting off with just this one field.
>
>***************************************************
>SELECT ;
>IIF(rx <> " " and location = " ",trim(name) + " " + trim(detail) + " Rx- " + trim(rx), ;
>IIF(rx <> " " and location <> " ",trim(name) + " (" + trim(location) + ") " + trim(detail) + " Rx- " + trim(rx), ;
>IIF(rx = " " and location <> " ",trim(name) + " (" + trim(location) + ") " + trim(detail), ;
>IIF(rx = " " and location = " ",trim(name) + " " + trim(detail),' ')))) as mdetail from hshealth
>***************************************************
>
>p.s.
>I tried breaking it up storing sections to variables, but get the same error message.
>
>Using variables:
>mselone = " SELECT IIF(rx <> ' ' and location = ' ',trim(name) + ' ' + trim(detail) + ' Rx- ' + trim(rx), "
>mseltwo = " IIF(rx <> ' ' and location <> ' ',trim(name) + ' (' + trim(location) + ') ' + trim(detail) + ' Rx- ' + trim(rx), "
>mselthree = " IIF(rx = ' ' and location <> ' ',trim(name) + ' (' + trim(location) + ') ' + trim(detail), "
>mselfour = " IIF(rx = ' ' and location = ' ',trim(name) + ' ' + trim(detail),' ')))) as mdetail from hshealth "
>
>then:
>&mselone + &mseltwo + &mselthree + &mselfour
>
>Am I way off base trying to use this method?
>
>Thanks,
>Jim Harvey