Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Posts
    14

    Question 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. #2
    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. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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. #4
    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. #5
    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.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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. #7
    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. #8
    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. #9
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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. #10
    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?

    Thanks in advance

  11. #11
    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 08:37.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  12. #12
    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. #13
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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. #14
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Unhappy 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.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

Posting Permissions

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