T
TRose
Guest
Hi Jon -
I'm new to posting to the Forum, but I'll try to be clear.
I'm designing a report based on a crosstab query showing 12 months of
telephone invoices.
Not all of the telephone numbers have invoices for each month, resulting
in null values.
The first row is the dollar amount per month (current$ + credit$).
Below this row, I'm trying to put 3 rows of calculations for each month:
Difference to prior month (i.e. Feb-Jan)
YTD Average (i.e. Jan+Feb/2)
Comparison to Average (i.e. Feb-YTD Avg)
I've converted the null values in the monthly total (1st row) to $0.00)
using a couple of different methods (IIf and Nz) successfully.
However, when any of the other rows encounter the now "$0.00", I can't
the calculation to produce anything other than $0.00.
If all months have Non Null values, the calculations work fine.
I've also tried a NullToZero function, but I get an error message:
Undefined function 'NullToZero' in expression: You entered an SQL
expression that includes a Function procedure name that can't be
recognized. Make sure the function exists, that it can be used in SQL
expressions, or check the expression to make sure you entered the name
correctly.
Any help would be appreciated.
Thx -
Rose
I'm new to posting to the Forum, but I'll try to be clear.
I'm designing a report based on a crosstab query showing 12 months of
telephone invoices.
Not all of the telephone numbers have invoices for each month, resulting
in null values.
The first row is the dollar amount per month (current$ + credit$).
Below this row, I'm trying to put 3 rows of calculations for each month:
Difference to prior month (i.e. Feb-Jan)
YTD Average (i.e. Jan+Feb/2)
Comparison to Average (i.e. Feb-YTD Avg)
I've converted the null values in the monthly total (1st row) to $0.00)
using a couple of different methods (IIf and Nz) successfully.
However, when any of the other rows encounter the now "$0.00", I can't
the calculation to produce anything other than $0.00.
If all months have Non Null values, the calculations work fine.
I've also tried a NullToZero function, but I get an error message:
Undefined function 'NullToZero' in expression: You entered an SQL
expression that includes a Function procedure name that can't be
recognized. Make sure the function exists, that it can be used in SQL
expressions, or check the expression to make sure you entered the name
correctly.
Any help would be appreciated.
Thx -
Rose
