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