how do you format a access table field to display value of zero?

J

JohnL

I'm trying to format a null table field to display zero. I've tried the
([nulltozero]) expression but have had no success.
 
D

Douglas J. Steele

There isn't a nulltozero expression: there's a built-in function Nz which
will convert a Null to a 0 (or a space) for display purposes, but you don't
use it as a format.

Your options are to create a query that uses the Nz function instead of just
the field in question, or to set the field's format to #,###;-#,###;0;0
assuming it's an integer. If there are decimal points, adjust those formats
accordingly. When you specify 4 separate formats, separated by semi-colons
like that, the first section applies to positive values, the second to
negative values, the third to zeros, and the fourth to Null values.
 
V

Van T. Dinh

Set the Format Property to something like (I use Long for the example):

0;;;0

The format string comprises of 4 parts separate by semilolon: first part for
positive numbers, second for negative, third for zero and fourth for Null.

OTOH, it is much less confusing not to display Null as zero. Perhaps, you
can use "Null" or at lease "Zero" for Null.

I think the more correct way is to set the DefaultValue of the Field to 0.
If the user doesn't enter a value, 0 will be saved instead of Null.
 
Top