By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

form not displaying accurate sums.

P: n/a
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.

Jan 10 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" <he**********@comcast.netwrote in message
news:39**********************************@i29g2000 prf.googlegroups.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.

Jan 10 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.