confounded by summary page not summarizing in Access 2003

[hlacroix]

I designed an Access database for a project.
It consists of tables, queries, forms, reports and macros.
Just about all the bugs have been worked out except for a summary page.
In addition to a client data entry form, I included a client work summary page which is intended to display a count, average and sum of session hours; a count, average and sum of optional hours and a grand total of both the session and optional hours-for an individual client.
the problem is that for instance, 3 entered for session hours and nothing entered in the space for optional hours does not yield a total of 3 (3+0=3).
Instead the total space is blank. If I enter 3 for session and 3 for optional, it's ok and I get 6. When I enter 3 for session and 0 for optional, however, it yields a total of 3, but it reports the 0 for optional as a session. Therefore when the averages are totalled, the result is 3 session hours divided by 2 sessions, because the zero is being counted as 1 session. the resulting answer is obviously wrong - (3+0=3)/2=1.5. The correct answer is obviously
(3+0=3)/1 =3. I think it has something to do with the null values but I'm stuck. Consider the sample zip attached.

Thank you very much for your input.

 

[boblarson]

Use the NZ function within the query.

 

[hlacroix]

Thank you Bob.

Would you elaborate a bit on the NZ function's specific placement in a query? I tried substituting = Nz([SessionHoursCompleted], 0) + Nz([OptionalHoursCompleted], 0) for
ttotals:Sum(([SessionHoursCompleted])+([OptionalHoursCompleted])) but it still isn't right.

 

[neileg]

Nothing wrong with what you have written. In what way is it not right?

 

[hlacroix]

Thanks for your reply, neileg.

The problem was that using the expression ttotals:Sum(([SessionHoursCompleted])+([OptionalHoursCompleted])), the two fields weren' adding correctly. I followed boblarsen's advice and substituted Nz([SessionHoursCompleted], 0) + Nz([OptionalHoursCompleted], 0) which yielded similar incorrect results. Later I determined that I needed to use, Sum((Nz([SessionHoursCompleted])+Nz([OptionalHoursCompleted]))). Now it works. I not very familiar with using expressions.
One thing I notice however; should this expression need 3 sets of parentheses? It seems that 2 should do.
Thanks again.