>when i did use this sql syntax
>Select Sum(IIF(!ISNULL(bal),bal,0)) As bal,accode,acname,"Opening Balance" As remname, wsdt As dt From PRINTopledger;
> GROUP By accode,acname HAVING Sum(IIF(!ISNULL(bal),bal,0))<>0
>
>Result: Sql query generated, but some of record in bal field appeared ********
>i think and changed sql syntax
>
>Select Sum(IIF(!ISNULL(bal),bal,0000000000.00)) As bal,accode,acname,"Opening Balance" As remname, wsdt As dt From PRINTopledger;
> GROUP By accode,acname HAVING Sum(IIF(!ISNULL(bal),bal,0))<>0
>
>Resuilt : Its Ok
>
>But it is also limited thinking can u help me about Permanent solution

First of all, you should start to simplify the following:

iif(!isnull(bal), bal, 0000000000.00)

is a little too complicated. For this purpose, we have the function nvl():

nvl(bal, 0000000000.00)

The result of the function nvl() is as follows: if the first part is null, the second part will be used instead. So, it is exactly equivalent to your iif(...), but simpler.

The reason the first part doesn't work is because you have an expression of a variable length - Visual FoxPro will determine the size of the field from the first record it analyzes. Therefore, sometimes this will work, sometimes it won't.

If the field is of type currency, you can simply use:

nvl(bal, $0)

... because Currency has a fixed size. Even if the result is not Currency, you could convert the entire result to Currency:

nvl(bal * $1, $0)
* or:
ntom(nvl(bal, 0))

This would ensure a fixed size. Note that Currency only allows 4 decimals.

On the other hand, since you are using Visual FoxPro 9, you might use the cast() function, which allows you to precisely control the data type of the result.

HTH,

Hilmar.