Null values

[Jake]

Hi,
I'm having trouble summing and formatting fields that contain Null values
and then formatting those controls on the report. I'm using currency
formatting on the underlying table. I created a field in the report query
using an NZ function i.e. Budget Revised: NZ([curBudgetRevised]), it does
allow the sum function on the report to evaluate the Null values, but it
doesn't allow currency formatting, even when I format for currency on the
report: $#,##0.00. I tried using the NZ function on the curBudgetRevised
field in the control on the report, but that won't work, I get a circular
reference error.
Thanks for any help!
Jake

 

[Duane Hookom]

I never use Nz() without including the second argument:
Budget Revised: NZ([curBudgetRevised],0)
If that doesn't work, try:
Budget Revised: Val(NZ([curBudgetRevised],0))

 

[Marshall Barton]

I'm having trouble summing and formatting fields that contain Null values
and then formatting those controls on the report. I'm using currency
formatting on the underlying table. I created a field in the report query
using an NZ function i.e. Budget Revised: NZ([curBudgetRevised]), it does
allow the sum function on the report to evaluate the Null values, but it
doesn't allow currency formatting, even when I format for currency on the
report: $#,##0.00. I tried using the NZ function on the curBudgetRevised
field in the control on the report, but that won't work, I get a circular
reference error.

How are you doing the summing? If you can use the Sum
function, then you should not be concerned with Null values
because all the aggrgate function ignore Nulls.

Formatting should be done in the thing that displays the
value, I think in this case a text box in the report. Null
values normally will not display anything, so I don't
understand what you're talking about with this, unless you
want to display some unexplained something for null values??
If that's what you're after, check Help starting with Format
Property and going from there.

The circular reference is caused by the text box control
having the same name as the field in the expression.

 

[Jake]

Thanks Duane!! Adding the 0 for the second argument was what I needed.

I never use Nz() without including the second argument:
Budget Revised: NZ([curBudgetRevised],0)
If that doesn't work, try:
Budget Revised: Val(NZ([curBudgetRevised],0))


--
Duane Hookoma
MS Access MVP
--

Hi,
I'm having trouble summing and formatting fields that contain Null values
and then formatting those controls on the report. I'm using currency
formatting on the underlying table. I created a field in the report query
using an NZ function i.e. Budget Revised: NZ([curBudgetRevised]), it does
allow the sum function on the report to evaluate the Null values, but it
doesn't allow currency formatting, even when I format for currency on the
report: $#,##0.00. I tried using the NZ function on the curBudgetRevised
field in the control on the report, but that won't work, I get a circular
reference error.
Thanks for any help!
Jake