Query about query of subtracting 2 fields

[Mary]

I've tried to update a field (say, fieldC) with the value = fieldA - fieldB
as follows:

under FieldC in the row of "Update To", I type the following:

[fieldA] - [fieldB]

For those records with values in both fieldA and fieldB, fieldC is correctly
filled with the subtraction value. However, for those with null value of
either fieldA or fieldB, null value is got in fieldC as well. But my
expectation should be as follows:

when
fieldA= 3,
fieldB = null,
the result I got in fieldC should be (3-0) = 3

Is there anything wrong with my expression? kindly help, thanks.

 

[John W. Vinson]

I've tried to update a field (say, fieldC) with the value = fieldA - fieldB
as follows:

under FieldC in the row of "Update To", I type the following:

[fieldA] - [fieldB]

For those records with values in both fieldA and fieldB, fieldC is correctly
filled with the subtraction value. However, for those with null value of
either fieldA or fieldB, null value is got in fieldC as well. But my
expectation should be as follows:

when
fieldA= 3,
fieldB = null,
the result I got in fieldC should be (3-0) = 3

Is there anything wrong with my expression? kindly help, thanks.

Yes. Null and 0 are TWO DIFFERENT VALUES.

0 is a clearly defined number - an integer between -1 and 1.

NULL is *undefined*. NULL means "this value is unknown, unspecified, it could
be anything". As such, 3 - <I don't know what this value is> does NOT equal 3
- it equals NULL, because 3 minus an unknown, unspecified value must give an
unknown, unspecified result.

You can use the NZ() - Null To Zero - function to convert a NULL value to 0
for this type of calculation:

NZ([FieldA]) - NZ([FieldB])


John W. Vinson [MVP]

 

[Mary]

I've tried and it work.

Thanks a lot!

I've tried to update a field (say, fieldC) with the value = fieldA -
fieldB
as follows:

under FieldC in the row of "Update To", I type the following:

[fieldA] - [fieldB]

For those records with values in both fieldA and fieldB, fieldC is
correctly
filled with the subtraction value. However, for those with null value of
either fieldA or fieldB, null value is got in fieldC as well. But my
expectation should be as follows:

when
fieldA= 3,
fieldB = null,
the result I got in fieldC should be (3-0) = 3

Is there anything wrong with my expression? kindly help, thanks.

Yes. Null and 0 are TWO DIFFERENT VALUES.

0 is a clearly defined number - an integer between -1 and 1.

NULL is *undefined*. NULL means "this value is unknown, unspecified, it
could
be anything". As such, 3 - <I don't know what this value is> does NOT
equal 3
- it equals NULL, because 3 minus an unknown, unspecified value must give
an
unknown, unspecified result.

You can use the NZ() - Null To Zero - function to convert a NULL value to
0
for this type of calculation:

NZ([FieldA]) - NZ([FieldB])


John W. Vinson [MVP]