How do I force null values to 0.00? 2

[steve728]

How do I force null values to 0.00?

I tried typing 0.00 in the format field w/o any success.

Steve

 

[THWatson]

Steve
Not sure about your structure, but you could try the Nz function

e.g.
Nz([YourField],0)

Tom

 

[dhookom]

You can use the format property to display a "0". Check help for Format Property - Numeric. A sample format property that would display "0" for Null:
0.00;(0.00);0.00;0

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Steve101]

I assume your field is defined as numeric.

(a) In the field definition, ensure that the DefaultValue property is set to zero. Also set the Required property to Yes. This will prevent Null entries from being allowed for the field for new or changed records.

(b) You could also use the BeforeUpdate event of the form to test the value and convert it to zero if it is null; eg. as provided in the post above:

YourField = nz(YourField,0)

This would ensure that your user interface automatically did the conversion without having to prompt the user to do it; though in most cases, the zero default would prevent the message anyway (unless the user changed the value to null).

There are other options, but these ought to suffice.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)