Access Query Problem

  • Thread starter Thread starter Hash
  • Start date Start date
H

Hash

I have a query based on 4 Tables. All tables are joined
with appropriate field name and it gives the result
correctly. For example, i have a Customer details in one
table, credit details in another table, debit details in
the 3rd table and opening balance from the 4th Table. My
query is supposed to result the balance amount. The
formula i used is "Opening Balance+Debit-Credit=Balance".
But, If a customer have no credit record, the balance
field shows null value. Then i posted an empty credit
record in the credit table with "0" Value. Then it gives
me the balance calculated. Is there any method to
caluclate the balance without posting blank records.

Thanks
 
Change your Balance calculated field formula to:
=Opening Balance+Debit-Iif(isnull([Credit]),0,[Credit])
and likewise for Opening balance and Debit if required.

HTH,
Nikos
 
I have a query based on 4 Tables. All tables are joined
with appropriate field name and it gives the result
correctly. For example, i have a Customer details in one
table, credit details in another table, debit details in
the 3rd table and opening balance from the 4th Table. My
query is supposed to result the balance amount. The
formula i used is "Opening Balance+Debit-Credit=Balance".
But, If a customer have no credit record, the balance
field shows null value. Then i posted an empty credit
record in the credit table with "0" Value. Then it gives
me the balance calculated. Is there any method to
caluclate the balance without posting blank records.

Thanks
Click to expand...

You can use the NZ() function to convert Null to Zero:

[Opening Balance] + NZ([Debit]) - NZ([Credit])
 
Back
Top