James,
By default STR function returns a string 10 character long but you need 4 to match hbstb and tent. You have to specify 4 as the second parameter for the STR function as shown below
select iif(hbstb<>" ",hbstb,iif(tent<>" ",tent,str(iif(barn>0,barn,0)
,4))) as stable from stnhorse order by hip_no
>Hi All,
>
>Is it possible to convert datatype in a multiple iif.
>
>hbstb c 4
>tent c 4
>barn n 1
>
>The below select does not give me an error message but only works 2/3.
>
>***begin select
>select iif(hbstb<>" ",hbstb,iif(tent<>" ",tent,str(iif(barn>0,barn,0)))) as stable from stnhorse order by hip_no
>***end select
>
>It does complete the stable field with data from both the hbstb and tent fields but it doesn't fill the stable field with any values from the barn field.
>
>I've tried numerous variations but to no avail.
>
>Any ideas would be appreciated.
>
>TIA
>
>Jim Harvey
--sb--