How to Simply Add Two Fields!

[accessman2005]

There should be a simple solution to this.
I have two fields with numbers in them ( dollars ).

I run a make-table query to add a third field that will show the sum of the two fields and my results in the third field are wrong.

Whenever there is a blank in the second field the third field shows a blank as well.

like this:

(Field 1 ) ( Field 2 ) (Field 3 = Field 1 + Field 2)
100.00 100.00 200.00
200.00 <blank> <blank>
130.00 100.00 230.00

my fields have the same data type and formatting so I don't understand where the problem is?

 

[d00ku]

The problem is in access you can't add a value with null so you have to equate the nulls to zero by either using IsNull() or Nz().

The following formula should work:

Sum(Nz([Field 1])+Nz([Field 2]))

 

[Pat Hartman]

1. Storing a calculated value is unnecessary and this case in particular where you are making a temporary table is way over kill. Just calculate the total in a query and use the query as the RecordSource for your Forms and Reports.
2. Null has special properties. Whenever you perform arithmetic or a conditional operation and one or more of the operands is null, the result is null. So -
If a = Null results in Null rather than True or False regardless of the value of a.
a + Null = Null
To get around the problem, use the Nz() function.
Select ..., Nz(fld1,0) + Nz(fld2,0) As Fld3