I think I answered my own question. The resultant field length, even with all the trimming is probably over the allowable field length (256 c)???

The reason I was trying to get all this information into one field was because I was going to use the data in a Crystal Report, and I thought it would be easier with one field...



>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