How can I convert a zero value in a table or form to read, "None".

D

Douglas J. Steele

You can't in a table: a field can only hold data of one type, so if it's a
numeric field (as it would have to be to hold a zero value), it cannot hold
a string "None".

However, you can FORMAT the field so that it appears to the user as the word
None,

Formats for numeric fields can have one to four sections with semicolons (;)
as the list separator. Each section contains the format specification for a
different type of number. The first section is the format for positive
numbers, the second is the format for negative numbers, the third is the
format for zero values and the fourth is the format for Null values.

If you specify the field's format as #;;"None", you'll get the word None
appearing everytime the value is 0. If you want it to say N/A when the value
is Null, you could use #;;"None";"N/A"
 
J

John Vinson

How can I convert a zero value in a table or form to read, "None"...
Click to expand...

There's an obscure trick using the Format property of a textbox to do
this: a Format property of a textbox bound to a number field can
contain *FOUR* different formats, for positive, negative, zero or NULL
values respectively. Set the Format property to

#;-#;"None";"<Nothing Entered>"

and you'll see the word None for zero, and <Nothing Entered> for blank
or empty fields. The NZ() suggestion will display NULLS as the
supplied value, but will not convert zeros.

John W. Vinson[MVP]
 
Top