I have a Credit and Debits report that shows an account’s transaction history. It is grouped by currency (e.g., US Dollar, Japanese Yen, Swiss Franc, etc…..).
In the each currency’s category header is a subreport with a Starting Balance field. It is calculated by a query which sums all the account’s transactions less than or equal to a start date, which the user inputs on a form – for each currency type.
In most cases, the starting balance will be a carryover value from the previous year. I have achieved this, no problem.
The problem is that if there are no transactions from the previous years and the currency account becomes active during the year (i.e. there will be transactions in the credit and debit history) the user wants the starting balance to begin at zero as of the start date. But since no records exist as of this date, the query is empty and thus the subreport is also empty.
Specifically, my problem is I need to conditionally value these nulls as zero.
Here is an example, assuming a Start Date of 1/1/2000:
An account holds US Dollars during 1999 and into 2000. The starting balance query calculates they have a starting balance of $10,000 on 1/1/2000. I can do this.
The account begins holding Japansese Yen on 1/31/2000 for the first time. How do I show a zero as of 1/1/2000? When in reality, no records exist i.e., it is null rather than zero.
And the account never has any CHF – so no Swiss currency group should be displayed i.e. these nulls should remain nulls.
In the each currency’s category header is a subreport with a Starting Balance field. It is calculated by a query which sums all the account’s transactions less than or equal to a start date, which the user inputs on a form – for each currency type.
In most cases, the starting balance will be a carryover value from the previous year. I have achieved this, no problem.
The problem is that if there are no transactions from the previous years and the currency account becomes active during the year (i.e. there will be transactions in the credit and debit history) the user wants the starting balance to begin at zero as of the start date. But since no records exist as of this date, the query is empty and thus the subreport is also empty.
Specifically, my problem is I need to conditionally value these nulls as zero.
Here is an example, assuming a Start Date of 1/1/2000:
An account holds US Dollars during 1999 and into 2000. The starting balance query calculates they have a starting balance of $10,000 on 1/1/2000. I can do this.
The account begins holding Japansese Yen on 1/31/2000 for the first time. How do I show a zero as of 1/1/2000? When in reality, no records exist i.e., it is null rather than zero.
And the account never has any CHF – so no Swiss currency group should be displayed i.e. these nulls should remain nulls.
