Calculating on Null Fields


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
 
Ad

Advertisements

F

fredg

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top