Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Summing all the Sum's to make a grand total

    Good day,

    I am tryinh to create a Grand total. I have fields in different subforms which contain a summed value. ie =sum([Extended_Price])

    I would like to add all the different Extended_Price fields together to create a grand total within a form.


    I tried the below code which works except when null values are present.
    When nulls are present I get an #Error in the text box.

    The code was placed in the control source property of the Grandtotal Text box.



    =Nz(Main_sub1.Form!Text1,0)+Nz(Main_sub2.Form!Text 2,0)+Nz(Main_sub3.Form!Text3,0)+Nz(Main_sub4.Form! Text4,0)+Nz(Main_sub5.Form!Text5,0)

    If you could let me know what I am doing wrong? Many thanks
    Marcus

  2. #2
    Join Date
    Nov 2010
    Posts
    84
    Can you attach an example of your database?

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Eremija, I can't at this stage sorry. Can i do anything else which will help?

  4. #4
    Join Date
    Nov 2010
    Posts
    84
    Perhaps the error in space field "Text 2" > "Text2"

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    sorry that was my mistake. I've corrected it now.

    =Nz(Main_sub1.Form!Text1,0)+Nz(Main_sub2.Form!Text 2,0)+Nz(Main_sub3.Form!Text3,0)+Nz(Main_sub4.Form! Text4,0)+Nz(Main_sub5.Form!Text5,0)


    returns #Error.

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Could someone help with the summation I'm trying to pull off?

    =Nz(Main_sub1.Form!Text1,0)+Nz(Main_sub2.Form!Text 2,0)+Nz(Main_sub3.Form!Text3,0)+Nz(Main_sub4.Form! Text4,0)+Nz(Main_sub5.Form!Text5,0)

    it returns #error.

    I tried just =Nz(Main_sub1.Form!Text1,0) which works for integers and zero but returns error on null values.

    Could it be that I am trying to add calculated fields? Text1 field is =sum([Extended_Price])


    thanks
    Marcus

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Provided that Main_sub1, Main_sub2, etc. are subforms, and that Text1, Text2, etc. are Textboxes with their ControlSource property set to
    Code:
    =Sum([Extended_Price])
    .
    1. In each subform, I would create a Total public property (or a public function):
    Code:
    Public Property Get Total() As Long   
    ' Public Function Total() As Long
    
        Total = Me.Text1.Value
    
    End Property
    2. In the module of the parent form, I can now write:
    Code:
    GrandTotal = Me.Main_Sub1.Total + Me.Main_Sub2.Total + ..., etc.
    If you have to handle decimal data types, declare the Total property (or the function) with the appropriate type (Single, Double, Currency...).

    Nz() should not be needed: according to Access Help, Sum ignores the records that contain Null fields.
    Have a nice day!

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi there, I'm not quite sure how to create a module in the parent form as you suggested.

    In the module of the parent form, I can now write:
    Code:
    GrandTotal = Me.Main_Sub1.Total + Me.Main_Sub2.Total + ..., etc.


    could explain a bit further please?

    I have a text box in the main form to display the grand total. Can this be used using the above code?


    thanks for your help
    marcus
    Last edited by marcusmacman; 12-02-10 at 11:06.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    When the parent form (i.e. the form that contains the subforms) is open in Design mode and active (blue band at the top of the Window containing it), click on the Code button of the toolbar or select "Code" in the View menu.
    Attached Thumbnails Attached Thumbnails Open_Access_Form_Module.jpg  
    Have a nice day!

  10. #10
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    Sorry to be lay.

    So I have put this code in the subform:-

    Public Property Get Total() As Long
    ' Public Function Total() As Long

    Total = Me.Text1.Value

    End Property

    where Text1 is the =sum([extended_Price]) field

    When I go to the parent form and click code it displays all the private Sub routines.

    I don't know where to put GrandTotal = Me.Main_Sub1.Total + Me.Main_Sub2.Total + ..., etc

    I tried to create an action button and used the on click event to trigger the code. I changed Grandtotal to the text box which will display the grand total, in this case 'txt_Grandtotal'

    when I run the code I get Run-time error 438 Object doesn't support this property or method. I looked at the value of txt_Grandtotal but it says Null.

    thanks
    Marcus

  11. #11
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    hi everyone,

    I could do with some help on my last question, if anyone has time to look at it?

    thanks
    Mark

Posting Permissions

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