dealing with null values

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

I'm creating a query with expressions to add a bunch of
fields toghether but some of the fields in the expression
of null values -- so the expression returns a null.

I'm afraid if I set the format in the field property to
substitute a zero for null I'll bloat my table. Is there
anyway to make the expressions ignore the null's? If not
I can't seem to find the correct format to force a
decimal number to substitute the 0 for null -- can
someone give me the expression.

For the currency fields I placed $0.00;($0.00)
[Red];$0.00;0.00
 
Ian,

You can use the Nz function to convert nulls to zero on-the-fly, in your calculated field,
without having to change the source field:

Calculated Field: Nz([Field1], 0) + Nz([Field2], 0) + Nz([Field3], 0) ....

where Field1, Field2 & Field3 are the names of the fields that may contain nulls.
I'm afraid if I set the format in the field property to
substitute a zero for null I'll bloat my table.
Click to expand...
Not likely. For one thing, applying a format never changes the underlying data. Formats only
change the way the data is displayed to the user.


Tom
__________________________________


I'm creating a query with expressions to add a bunch of
fields toghether but some of the fields in the expression
of null values -- so the expression returns a null.

I'm afraid if I set the format in the field property to
substitute a zero for null I'll bloat my table. Is there
anyway to make the expressions ignore the null's? If not
I can't seem to find the correct format to force a
decimal number to substitute the 0 for null -- can
someone give me the expression.

For the currency fields I placed $0.00;($0.00)
[Red];$0.00;0.00
 
Post details of relevant Tables and the SQL String of your Query including
the "expression".
 
I'm afraid if I set the format in the field property to
substitute a zero for null I'll bloat my table. Is there
anyway to make the expressions ignore the null's?
Click to expand...

Tom's suggestion if using the NZ() function is the simplest
way to do this. Just one note though - the Format property
*only* controls how the value is displayed. It does not
affect what's stored in the table nor what is used in
calculations in any way whatsoever. Using a Format to
display NULL as zero won't hurt your database, won't help
the calculations, but it may mislead the user since they'll
*see* a 0 but not be able to search for 0 or to do
calculations with it.

John W. Vinson (sneaking in from vacation)
 
Back
Top