Results 1 to 5 of 5
  1. #1
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    136

    sum in vba

    I am a newbie to Access VBA so excuse me for asking what some may deem to be a simple question.



    Anyways, I am trying to get a total by summing 4 different fields in my form using VBA.

    Here is my code:

    Code:
    Private Sub Command56_Click()
    Me.Total = Cost_of_White_Wine.Value + Cost_of_Red_Wine.Value + Cost_of_Rose_Wine.Value + Cost_of_Other_Wine.Value
    End Sub
    but it's concatenating the values instead of adding it.

    Can someone please help me?

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The simplest solution is:

    Code:
    Me.Total = Sum([Cost_of_White_Wine] + [Cost_of_Red_Wine] + [Cost_of_Rose_Wine]+ [Cost_of_Other_Wine])
    The .Value can be omitted as it's the default value

    However the above code will fail if any of the fields are NULL
    Using the Nz function as below solves that issue:

    [CODE]Me.Total = Sum(Nz([Cost_of_White_Wine],0) + Nz([Cost_of_Red_Wine],0) + Nz([Cost_of_Rose_Wine],0)+ Nz([Cost_of_Other_Wine],0))/CODE]
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    mp3909 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    136
    That did not work.
    I changed my code to your suggestion but now getting a compile error saying sub or function not defined and it is highlighting the sum part.

    Code:
    Private Sub Command56_Click()
    Me.Total = Sum(Nz([Cost_of_White_Wine], 0) + Nz([Cost_of_Red_Wine], 0) + Nz([Cost_of_Rose_Wine], 0) + Nz([Cost_of_Other_Wine], 0))
    End Sub

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    This should work:
    Code:
    Me.Total = Val(Nz([Cost_of_White_Wine], 0)) + Val(Nz([Cost_of_Red_Wine], 0)) + Val(Nz([Cost_of_Rose_Wine], 0)) + Val(Nz([Cost_of_Other_Wine], 0))

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry - I gave you the OVERALL total rather than the total for each record - brain not engaged earlier!

    Either of these will give the same results - the 2nd formula is that given by Davegri earlier

    Code:
    =Nz([Field1],0)+Nz([Field2],0)+Nz([Field3],0)
    Code:
    =Val(Nz([Field1],0))+Val(Nz([Field2],0))+Val(Nz([Field3],0))
    See below and attached:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	16.4 KB 
ID:	32879

    NOTE: There are 2 d.p. for each as I used fixed number format for the totals textboxes

    EDIT: What datatype are you using for your 4 cost fields?
    Attached Files Attached Files
    Last edited by isladogs; 03-04-2018 at 03:03 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums