473,320 Members | 2,193 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Jan 10 '08 #1
1 1226
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Susan Bricker | last post by:
I have a group section footer in a Report in which I want to display various "counts" (not sums). For example: some of the fields in the recordset from the query are: score = integer qualified...
3
by: slylos | last post by:
I've got a section of code in my app that keeps track of how much time an employee has accrued, which equals out to 1.33 days of PTO per month. I'm trying to store the time accrued so far in an...
3
by: corear | last post by:
I have a new case tracking system. The cases table has these fields: CaseID (Primary Key) StatusID (looked up in another table - Open, Pending, or Closed) Status Comment (memo field) Date&Time...
8
by: DrewKull | last post by:
Hello, I have a query which sums check boxes (so 0 or -1) then Abs the sum so i can get the number of check box 'checked' per column... based on start and end date. SELECT Abs(Sum(CommCon)) AS ,...
6
by: KiwiGenie | last post by:
Hi..I am trying to make a search form. I am fairly new to access and could well be looking at it completely wrong. I have an unbound form with textboxes in the header for entering different search...
1
by: wisemen | last post by:
I have a table with 2 columns, and .I want to run a query that will give me a cumulative sum of the no. of entries that have an <= and on the second column give me a cumulative sum of the no. of...
4
by: Kurrent | last post by:
I have some data from text fields that are being passed over through a form that I am displaying with the $_POST superglobal. Once i have echo'd out this data onto the next page, i'd like to...
6
mbmccormick
by: mbmccormick | last post by:
This picture pretty much sums up my problem... Notice the display of the font size in the description on the side. This is occurring in just about every Windows dialog I use. This does not occur in...
2
geolemon
by: geolemon | last post by:
I'm still relatively new to Access - this question is due to my moderate experience with forms and my complete and total lack of experience with reports. I want to create a report (I use that term...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.