Access Query Problem

[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

 

[Nikos Yannacopoulos]

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

 

[___]

Why not set the default value for the credit column in the credit tabl
to "0"

HT

 

[John Vinson]

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

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

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