Adding the values of 2 fields (2002)

Topic

New Reply

I’m sure the answer is easy, but I can’t find it. I created a query that calculates separately the sum of two decimal fields. In the report, I am adding these two fields to come up with the grand total. It works as long as there are values in both fields, but if one is empty, it doesn’t do the sum. My math tells me that 1+0=1, so I don’t understand why it doesn’t work. Is there a way of doing this without creating an “If” statement?

Reply | Quote

Replies

Use the Nz Fuction.
Total : Nz([Field1],0) + Nz([Field2],0)
Nz will replace the Null by zero in the calculation.

Reply | Quote

Francois already provided the solution.

As an explanation: an empty field (“Null” in database terms) is not the same as a zero value. It means that the value is missing, lacking. Any calculation direclty involving Null values will return Null, indicating that there’s not enough information. If you want to force Access to treat Null values as zero, you need to use the Nz function, as suggested by Francois.

Reply | Quote

Thanks. Worked like a charm. What fooled me was the default values of the fields in the table. I had them set as zero, which to me means that the field is not null, but the numerical value zero. Obviously, this is not the case.

By the way, is there a casebook on Access written? I have a couple that are as thick as Bibles, but they are boring, too technical (above my knowledge level) and not project driven. I know enough about Access to be dangerous, but still struggle with many aspects. My strength is to take complex ideas and explain them simply. For example, I am still struggling with the cascading combo boxes. If there was a book that talked about how Hans was building a database to do x, y and z, and here is what he did step by step to achieve it. I know every situation is different, but there must be something we can do.

What are some examples of projects that come to mind?
A school with teachers, courses, students, marks, timetables (which is sort of what I am building now).
A football league with teams, players, schedules, games, scores, statistics, standings.

Got to run now. I am going to teach soon.

Reply | Quote