formatting numbers after using the nz function

[jlarson]

i created an outer join in access 2003. The results work fine, and I get the
expected null fields. I can format the resulting numbers in the query and
in a report as standsrd with two decimal places. I then used the nz function
to change the nulls to zeros, which works fine but afterwords I cannot format
the numbers in either the query or the report. For example, instead of
displaying a number like 10,000.00 as I would expect I get 10000.0000. I am
sure I have done this successfully in the past, but it won't work for me now.

 

[John Spencer]

Using NZ in a query often has the unfortunate effect of turning a number
into a string containing number characters.

Try an expression like the following in your query

CCUR(NZ(SomeField,0))

Or use CDbl instead of CCur if you need more than 4 decimal places.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[jlarson]

Thanks John Spencer. It worked great, though I am still wondering. I have
been using the nz function in my access databases for more than a decade and
this is the first time I have had this problem.

 

[Rick Brandt]

Thanks John Spencer. It worked great, though I am still wondering.
I have been using the nz function in my access databases for more
than a decade and this is the first time I have had this problem.

First time you've noticed it. It's always been that way as far as I know.

 

[John Spencer]

It is subtle. Especially since Access can treat a string of number characters
as a number in many cases. So it can often go unnoticed for an extended period.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County