>Dear Sir,
>
>The code field is character type
>You wrote
>

>SUM(IIF(VAL(INT(t2.code/1000)) = '3201',t2.cr_amount,0)) as amt1,;
>

>and to change character to number I modified as
>

>SUM(IIF(INT(VAL(t2.code/1000)) = '3201',t2.cr_amount,0)) as amt1,;
>

>But it still shows error "Data type mismatch"
>
>Please help again

Cetin assumed the "code" field type to be numeric, you can avoid such misunderstandings by describing your data in DDL code. e.g.:

Create Cursor test (code Char(10), name Char(30), qty Int, credit N(12,4), DebitN(12,4), code2 Char(10))
Insert Into test Values ('14','Eric',...

When "code" is a character type, you can use Left(Nvl(code,''),4) instead of the Val(Int(code/1000)) construction.

As for the data design: your querys is getting complicated because your data structure is not "normalized" http://en.wikipedia.org/wiki/Database_normalization


hth
-Stefan