Another option may be to create a UDF that way you can make the expression as complicated as you want.
HTH
CAroline

>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