I am a new user in access and created a database to use in accounting practice. I want to create a query control that will show the opening balance, plus fees less closing balance = 0 as all data are imported from another system.

The problem is that the closing balance does not contain a full list of clients and when I run the query I only get partial data. I hve tested by first trying to break the query down to more simple test.

I have a complete client list table with 0 balances and then created a query that should add the closing balance and the 0 balance per clientlist table together in order that every client will have an entry either 0 if it IsNull in the closing balance query or the balance as per closing balance query.

Here is the expression I built but it does not work as I do not get any 0 balances returned, I still only get balances for clients on the closing balance query but nothing for clients that is not on the closing balance query. Hope it makes sense. Please help

Expr1: IIf(IsNull([Qry:Sum Imported CLosing Balance]![SumOfValue]),[Tbl: Clientlist Zero Balances]![Balances],[Qry:Sum Imported CLosing Balance]![SumOfValue])

7 Years
Discussion Span
Last Post by ChrisPadgham

I don't quite get the problem are you saying that you are missing rows. if this is the problem and you are joining two tables you should check you are using an outer join.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.