form not displaying accurate sums.

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 tried inserting the NZ function into the query but
it's still now right. Consider the sample zip attached.

Re: form not displaying accurate sums.

If you are adding the contents of Controls on a Form, and there is nothing
in one of the controls (it is a Null value), which is what I understand from
your description, then it is working correctly -- a Null is _undefined_ or
_unknown_ and any calculation with a Null yields Null as the result.

If you want an unfilled Control to calculate as zero rather than Null, use
the NZ builtin function (that's Null-to-Zero)... instead of a calculation
like:

myvar = Me.txtSalePrice - Me.txtSpecialDiscount

use

myvar = NZ(Me.txtSalePrice) - NZ(Me.txtSpecialDiscount)

If that doesn't solve your problem, then you probably are going to have to
be more specific on what you have, how you are doing the calculations, etc.,
if anyone is to be able to do more than (as I did) guess at a suggested
solution.

Larry Linson
Microsoft Access MVP




"cgiscri" wrote in message
news:394d4a9b-e537-49d1-bf70-74f3acbd5ba7@i29g2000prf.google groups.com...
>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 tried inserting the NZ function into the query but
> it's still now right. Consider the sample zip attached.
>