Dealing with nulls in left join.

[Dellarella]

I am new to this forum and to Access so I appreciate the opportunity to be able to ask you all a question. I am trying to create a financial statement. My tables are Chart of Accounts and GL Transactions. I have created a Trial Balance table where the beg bal, debit, and credit fields all have default values of '0'.

Using a left join query, I appended Trial Balance with all the fields from Chart of Accounts and fields from GL Transactions where data exists. However, when I do this, the default values in Trial Balance disappear leaving null fields, making it impossible to calculate my ending balances.

I have tried several different things including trying to change the nulls to zero, and trying to set the default ending balance value as the beg bal value, but I am getting nowhere. Any help you all can offer would be most appreciated.

 

[student]

I'd suggest looking at the nz() function if you haven't already:

http://office.microsoft.com/en-us/access-help/nz-function-HA001228890.aspx

but if you have then prob best to post your query code here for the experts to check over.

One issue I had recently (pulling from tables in Sage) was that the fields causing issues were not nulls, but 8-character strings like " " so it's probably worth checking this as well.

 

[Dellarella]

I will check it out. Thank you for your time!

 

[student]

No problems. Sage can be a bit funny like that. Let us know how you get on.

 

[Dellarella]

ok..After researching and trying to use the Nz function, and researching and trying to use the Nz function(I tried several different times. lol), I tried another approach with the IIF function. IT WORKED!

As a way to hopefully help others, I will show what I did:
I built the query in design view. The name fields of the debit and credit columns look like this:

Debits:IIf([sumofgldebt] is null, 0, [sumofgldebt])
Credits:IIf([sumofglcred] is null, 0, [sumofglcred])

This puts the zeros in where there is a null. I am then able to run another query and perform calculations on these fields!

Success! Thanks again Student for your help.