difference between two fields in a query

[naggappan]

hi i have a query created for a table with 3 fields like,
1)name 2)sum of credit 3)sum of debit
Now i need an extra field like
4)sum that is [sum of credit]-[sum of debit] but when i give this in a query if the difference is there then it is coming but if there is no value in debit then the value in credit should come or if no value in credit - value should come

NOW I GET LIKE THIS

NAME SUMOFCREDT SOMEOFDEBIT sum

Tom 100
purchace 5000
salse 1000
jon 4000 1000 3000


BUT I SHOULD GET LIKE

NAME SUMOFCREDT SOMEOFDEBIT sum

Tom 100 100
purchace 5000 5000
salse 1000 -1000
jon 4000 1000 3000
Peter 1000 4000 -3000

 

[Galaxiom]

Nz([sum of credit])-Nz([sum of debit])

 

[naggappan]

Thanks it worked for me good now. And one more thing i am trying to do . (i.e) if the calculated answer is in minus it should go to debit or if it is plus it should go to credit. For this i can create a new query with 3 fields name credit and debit but the values of this plus and minus should be taken form here and put over there in my new query, So that i could create an report easily the total of the name account is in credit or in debit (i.e) plus should be in credit and minus in debit... can you get my point what i am trying to say .. please replay me for this a logic

 

[Galaxiom]

You can do it in the same query.

Credit: IIF((Nz([sum of credit])-Nz([sum of debit]))>0, Nz([sum of credit])-Nz([sum of debit]), Null)

I expect you can come up with the Debit expression from this.

 

[naggappan]

Thanks dude it worked for me..