Nz removes format

  • Thread starter Thread starter jr1956
  • Start date Start date
J

jr1956

I have 2 expressions that uses Nz to populate Null data to
0. This works very well but, it removes the format. Both
of the expressions are below. The first expression is set
in the properties to be currency format and the second is
set to percentage. Did I put Nz in the wrong syntax?

Currency example

Year To Date Balance: Nz([tblBudgetSubtype]!
[OpticalBudgeted]-[Expenditures Concat]![SumOfAMOUNT],0)

Percent example

Pct of Budget Spent: Nz(IIf([tblBudgetSubtype]!
[OpticalBudgeted]=0,0,[Expenditures Concat]!
[SumOfAMOUNT])/IIf([tblBudgetSubtype]![OpticalBudgeted]
=0,1,[tblBudgetSubtype]![OpticalBudgeted]),0)

Thanks Dennis
 
Do you need to do calculations on the values? If not, wrap the Format
function around them:

Year To Date Balance: Format$(Nz([tblBudgetSubtype]!
[OpticalBudgeted]-[Expenditures Concat]![SumOfAMOUNT],0), "Currency")

Pct of Budget Spent: Format$(Nz(IIf([tblBudgetSubtype]!
[OpticalBudgeted]=0,0,[Expenditures Concat]!
[SumOfAMOUNT])/IIf([tblBudgetSubtype]![OpticalBudgeted]
=0,1,[tblBudgetSubtype]![OpticalBudgeted]),0), "Percent")

Alternatively, you can set the Format property of the Query fields.
 
Back
Top