Calculating on Null Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that contains four currency fields (field1, field2, field3,
field4). The last field (field4) performs a calculation using data from the
other three. Basically, Field 2 is subtracted from Field 1, then field3 gets
added to that. Here's the expression I'm using in field4.

Portfolio Total: [field1]-[field2]+[field3]

The problem is, for some records field2 and/or field3 are blank. If either
of those fields is blank, then nothing appears in field4. I tried using nz in
the SQL to insert default values into field2 and field3, but it doesn't help.
How can I force the expression to calculate, regardless whether the fields
being used contain null values?

Many thanks,
Gwen H
 
I have a query that contains four currency fields (field1, field2, field3,
field4). The last field (field4) performs a calculation using data from the
other three. Basically, Field 2 is subtracted from Field 1, then field3 gets
added to that. Here's the expression I'm using in field4.

Portfolio Total: [field1]-[field2]+[field3]

The problem is, for some records field2 and/or field3 are blank. If either
of those fields is blank, then nothing appears in field4. I tried using nz in
the SQL to insert default values into field2 and field3, but it doesn't help.
How can I force the expression to calculate, regardless whether the fields
being used contain null values?

Many thanks,
Gwen H
Click to expand...

Portfolio Total: Nz([field1],0)-Nz([field2],0)+Nz([field3],0)
 
Back
Top