I am sorry. I don't understand.

Are you saying that you do:

select iif(flag = "Income", 1,0)*amount as Income, iif(flag = "Income", 0,1)*amount as Payments;
....;
union;
select iif(flag = "Payment", 0,1)*amount as Income, iif(flag = "Payment", 1,0)*amount as Payments;

and you get only Income?

If you do just:
select iif(flag = "Income", 1,0)*amount as Income, iif(flag = "Income", 0,1)*amount as Payments;
.....

Of course, you only get Income. I can't see what this has to do with multiplying by big number?

Sorry, can you explain more?


Tai


>>It works. Thx.
>>
>>In fact, I just multiply by 1 at both the SELECT and it works. Any reason for multiplying and dividing the big number. It seems to make the field big.
>>
>>
>I realy don't know why they designed it that way but sometimes this odd behaviour can drive you crazy if you don't use this silly (and it works !) solution. One example is storing all earnings and payments in one field using another as a flag.
>
>Amount Flag
>100000 Income
>200000 Payment
>200000 Income
>......
>
>select iif(flag = "Income", 1,0)*amount as Income, iif(flag = "Income", 0,1)*amount as Payments
>
>leads to many income and 0 payment (or reverse if the first is Payment). Again using a big number enough as a multiplier instead of 1 resolves the problem (even if in VFP5 it's still like that). And this behaviour is true for character fields that are based on an expression rather than a field (and I use padr() or something like that for those).
>Cetin