# Thread: Sum of Fields In a form?

1. Registered User
Join Date
Sep 2003
Posts
14

## Unanswered: Sum of Fields In a form?

I'm relatively new at access, I was wondering if it's possible to sum four fields and have the total in a new field.

My fields are for calculating the man days used, they're done by quarter and I'm needing a running annual total.

Field names are:

MDQ1
MDQ2
MDQ3
MDQ4
MDTOTAL

2. Registered User
Join Date
Apr 2003
Posts
280

## Re: Sum of Fields In a form?

Originally posted by RickyB77
I'm relatively new at access, I was wondering if it's possible to sum four fields and have the total in a new field.

My fields are for calculating the man days used, they're done by quarter and I'm needing a running annual total.

Field names are:

MDQ1
MDQ2
MDQ3
MDQ4
MDTOTAL
MDTOTALum([MDQ1]+[MDQ2]+[MDQ3]+[MDQ4])

3. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
Since you are new at Access, this tip may be helpful to you. To ensure that a blank field does not give unexpected results when used in a calculation, use:
MDTOTALum(Nz([MDQ1])+Nz([MDQ2])+Nz([MDQ3])+Nz([MDQ4]))

In effect this treats a blank numeric field as if its value were zero. You can't do arithmetic with a null value. Without the Nz function, if a field was blank (null), that record's other values would not be included in the sum total.

Jerry

4. Registered User
Join Date
Apr 2003
Posts
280
Is the Nz function is part of Visual Basic as well not VBA. I never saw it before..

5. Registered User
Join Date
Sep 2003
Location
UK
Posts
122

## Re: Sum of Fields In a form?

Originally posted by RickyB77
I'm relatively new at access, I was wondering if it's possible to sum four fields and have the total in a new field.
Do you intend just to report the result (in a query or on a form) or are you going to try and store the data?

I ask only because you would be best avised to have the information diplayed only when needed by calculating it "at runtime".

If you calculate it on a form then you would be well advised to use the On_lostFocus and On_GotFocus events (see the properties list) of the text box(es) of MDQ1, MDQ2, MDQ3, MDQ4 and have them call the calculator function again. This way any changes are automaticly updated in the totals box.

6. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
Originally posted by lansing
Is the Nz function is part of Visual Basic as well not VBA. I never saw it before..
Suggested solution:
MDTOTALum(Nz([MDQ1])+Nz([MDQ2])+Nz([MDQ3])+Nz([M
DQ4]))

You can find information on the Nz function in Access help; use the index to locate "Nz". It can be used to return 0 (zero) when a control value or field value is null. This will prevent errors in your calculation should one of the fields be blank.

Jerry

7. Registered User
Join Date
Sep 2003
Posts
14
Hey guys,

The function looks like it'll work great. Like I said, I'm not too bright at this stuff and I don't know where to put this formula. ValidationText?? OnLostFocus?

Thanks,

Rickyb

8. Registered User
Join Date
Sep 2003
Posts
8
Hi, sorry for interruption, but um facing a similar problem so I might gain some help from ur discussion

I tried ur solution but it does not work, i get something (?Name)

I wrote this instead at the control source

= sum ([A] + [B])

but it calculates the first record wrong, but the rest is OK

9. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
To KTKT79:

This works for me as a control source for a list box, where you want to display the sum of fields:

SELECT [Table1].[Name], Sum(((Nz([Value1])+Nz([Value2])))) AS Calc FROM Table1 GROUP BY [Table1].[Name];

The RowSource Type is Table/Query.

Jerry

10. Registered User
Join Date
Sep 2003
Posts
8
Hello, Thank you very much 4 ur help

The problem is that I want to use it in an unbound text box and not a listbox control, in addition to that the source of the calculated fields is a query & not a table. I didn't know how to utilize your code to make it workable in my cause.

Any help?

11. Registered User
Join Date
Sep 2003
Location
UK
Posts
122
Originally posted by KTKT79 The problem is that I want to use it in an unbound text box and not a listbox control, in addition to that the source of the calculated fields is a query & not a table. I didn't know how to utilize your code to make it workable in my cause.
One assumes you are useing the query builder (design mode) from access.

in the first blank column were you would normally select a field from the tables you are querying type
Code:
`MDTOTAL: Sum(Nz([MDQ1]) + Nz([MDQ2]) + Nz([MDQ3]) + Nz([MDQ4]))`
now on the Form bind the textbox to the new "field" that has appeared It will be called MDTOTAL.
Last edited by Matt_T_hat; 09-19-03 at 07:37.

12. Registered User
Join Date
Sep 2003
Posts
8
Hi Matt_T_hat

This is very strage!

I did it your way, but I still get the first record calculated wrong! But when I tried writing this code in the Control Source (the first blank column in Data tab) by using the Expression Builder:

=(Nz([ProductID])+Nz([OrderID]))

It works fine!

I really don't understand the cause of my problem, although the 'sum' code looks reasonable!

I hope if any one could explain the cause of my problem to do so.

Thanks so much for the help Matt_T_hat

13. Registered User
Join Date
Jan 2002
Location
Bay Area
Posts
513
To KTKT79
I'm over my head for recommending the correct way to get the results of a query into a text box. If I had to do it, I would put the single calculated sum into a hidden list box, which gets the value from a query; then in the form open event, assign the text box text the value from the hidden list box. This is only a guess. I don't think it would fly with the pro's.

Form Open Event:
Text1.Setfocus
Text1.Text = List1.ItemData(0)

Jerry

Originally posted by Matt_T_hat
One assumes you are useing the query builder (design mode) from access.

in the first blank column were you would normally select a field from the tables you are querying type
Code:
`MDTOTAL: Sum(Nz([MDQ1]) + Nz([MDQ2]) + Nz([MDQ3]) + Nz([MDQ4]))`
now on the Form bind the textbox to the new "field" that has appeared It will be called MDTOTAL.

14. Registered User
Join Date
Sep 2003
Location
UK
Posts
122

## ooops....

Originally posted by KTKT79
This is very strage!
I did it your way, but I still get the first record calculated wrong! But when I tried writing this code in the Control Source (the first blank column in Data tab) by using the Expression Builder:

=(Nz([ProductID])+Nz([OrderID]))

I really don't understand the cause of my problem, although the 'sum' code looks reasonable!
I may have explained poorly. not to mention possibly made an error. The code I'm quoting you quoteing is a WHERE clause. It would go in the fourth or fifth row down.

The Code I mentioned and others have mentioned before me goes in the top Row where the field name would be.

If you check the SQL version of you query you should be seeing something like

Code:
`... MDtotal AS [Field1]+....`
Haveing double checked the code quoted there is a small chance of error. SUM does not mean Do this Sum but Sumation (Add it all up). so Sum(A, B, C) == A + B + C.

I should have double checked that before I continued to quote someone elses code.

Code:
`MDTOTAL: Nz([MDQ1]) + Nz([MDQ2]) + Nz([MDQ3]) + Nz([MDQ4])`
If you want to have the query total up that colum to get one figure for everything listed then do the following:

Click the SUM button it looks like a very odd E crossed with a K.

|--_
>
|---

Sorta like that duff ascii art.

Press it and a new set of rows will appear they should all say "Grouped By". Change the one for MDTotal to SUM.

Now for every different set of combinations of selected fields there will be a sum.

To get the total for a given person remove everything but the person ID field so you have PersonID, MDtotal and nothing else. You will get the sum of hours used per person over the 4 Quarters.

If you want a single result just one number use only the MDtotal field with sum selected.

If you have a quick look at the SQL you should also now see

Code:
`... MDTotal AS Sum(Nz([MDQ1]) + Nz([MDQ2]) + Nz([MDQ3]) + Nz([MDQ4])) ...`
or something very similer

Try it and see what happens. If it goes wrong view the SQL and post it here.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•