Get a Range of Cells in Access??  Using Excel's SUM function in Access

Posted on 2007-10-17
Last Modified: 2013-11-28
Using VB, how can I select a range of cells in Access?  For Example, If I wanted to use the SUM function on a range of cells in a table that resides in Access.  How could I do that in VB?

Question by:JoshinHtown
    LVL 58

    Expert Comment

    If you mean a rectangular area, this will prove difficult. If you want to sum one column, you can do so with a total query, a report with grouping, or a form with a footer.

    Besides, Excel's SUM function relies on the fact that blank cells are Empty. In Access, blank cells are Null, and that would break the result.

    Could you explain a bit more?
    LVL 61

    Expert Comment

    use DSum:

    DSum("YourField", "YourTable")

    You can also specify criteria in a third optional argument.  This works on columns, not rows.

    Author Comment

    Hello all-  Sorry for not being specific.  If I had a column named OctoberVal and another column named NovemberVal and I wanted to use the SUM function to get that value.  Lets say I selected a value in a combobox and the combobox is bound to a table in the access database.  Upon selecting the value there is just one value in OctoberVal and one value in NovemberVal.  I just need those added together using the SUM function in excel.  I know there is an easier way of doing this but I need to use the excel function method for reasons I won't get into.  Hope that makes sense.

    LVL 6

    Expert Comment

    Hi Josh, are you trying to pull data from Access into Excel, or pull the data onto an Access form?  If you're pulling from Access into Excel, you will probably need to create a query in Excel:

    Data->Import External Data->New Database Query
    Select "MS Access Database"
    Browse to your .mdb file
      (this will pull up a list of the tables in the .mdb)
    Select which column(s) you want, press Next
    Enter any filter criteria you need (i.e., which Rows you want), press Next
    Select a sorting option or leave it blank, press Next
    Select the top radio button, "Return Data to Excel", press Finish or save the query for future use

    This will pull all of the appropriate rows into Excel and you can do a simple =sum function there.  If you want to pull a sum into an unbound texbox on an Access form, try the DSUM function:

    Hope this helps,

    Author Comment

    The access form is in Access and the table I will be pulling the range of data from is in a table binded to the combobox in the form.

    Hope this helps
    LVL 58

    Expert Comment

    > I need to use the excel function method for reasons I won't get into.

    That is too bad, I think I'd like to hear that... Anyway, here is a simple way to call any Excel function:

    Function XLSum(Arg1, Optional Arg2, Optional Arg3, Optional Arg4, _
        Optional Arg5, Optional Arg6, Optional Arg7, Optional Arg8, _
        Optional Arg9, Optional Arg10, Optional Arg11, Optional Arg12, _
        Optional Arg13, Optional Arg14, Optional Arg15, Optional Arg16, _
        Optional Arg17, Optional Arg18, Optional Arg19, Optional Arg20, _
        Optional Arg21, Optional Arg22, Optional Arg23, Optional Arg24, _
        Optional Arg25, Optional Arg26, Optional Arg27, Optional Arg28, _
        Optional Arg29, Optional Arg30 _
        ) As Double

        Static XL As New Excel.Application
        XLSum = XL.WorksheetFunction.Sum(Arg1, Arg2, Arg3, Arg4, Arg5, _
            Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, Arg13, Arg14, _
            Arg15, Arg16, Arg17, Arg18, Arg19, Arg20, Arg21, Arg22, Arg23, _
            Arg24, Arg25, Arg26, Arg27, Arg28, Arg29, Arg30)

    End Function

    This isn't very efficient, as the function stored its private copy of Excel, but you get the idea. The declaration mimics the Excel declaration exactly, so that anything you can pass to Excel's SUM can be passed here as well.

    I have created a form with two controls: OctoberVal and NovemberVal. The simple method is to use this in a third text box:


    And you can also use:


    I'm not sure this really answers your question, though. Note that there are much faster implementations of XLSum if you want, not using Excel at all...


    Author Comment

    Hi again harfang-   This seems promising.  Can this also work for the FORECAST function?  

    Function XLForecast(Arg1, Optional Arg2:OptionalArg3, Optional Arg4:OptionalArg5) As Double

       Static XL As New Excel.Application

       XLForecast = XL.WorksheetFunction.Sum(Arg1, Arg2:Arg3, Arg4:Arg5)

    End Function
    LVL 58

    Accepted Solution

    In fact, this would be:

    Function XLForecast(Arg1 As Double, Arg2, Arg3) As Double
        Static XL As New Excel.Application
        XLForecast = XL.WorksheetFunction.Forecast(Arg1, Arg2, Arg3)
    End Function

    But you should really use the same XL instance for all your XL formulas, if you really choose to go that route... So this seems better:

    Function XL() As Excel.Application
        Static sXL As New Excel.Application
        Set XL = sXL
    End Function

    Function XLForecast(Arg1 As Double, Arg2, Arg3) As Double
        XLForecast = XL.WorksheetFunction.Forecast(Arg1, Arg2, Arg3)
    End Function

    For any new function you need to implement, continue like that. And remove the line

        Static XL As New Excel.Application

    From the previous XLSum function as well. Both functions can now use the same instance, which is preferable in terms of memory usage and loading time.

    You can then call your new function like this:

        ? XLForecast(20, Array(120,135,150,108),  Array(12,13,17,10))

    I'll leave the problem of feeding data from an actual table as an exercise -- (^v°)

    However, once more, this isn't efficient. Instead, create your own database Forecast function. Consider this:

    Function DForecast( _
        dblNewX As Double, _
        pstrData As String, _
        pstrFieldX As String, _
        pstrFieldY As String _
        ) As Double

        Dim strSQL As String
        Dim dblA As Double
        Dim dblB As Double
        strSQL _
            = " SELECT Count(*) As N," _
            & "   Sum(" & pstrFieldX & ") As SX," _
            & "   Sum(" & pstrFieldX & "^2) As SX2," _
            & "   Sum(" & pstrFieldY & ") As SY," _
            & "   Sum(" & pstrFieldY & "^2) As SY2," _
            & "   Sum(" & pstrFieldX & "*" & pstrFieldY & ") As SXY" _
            & " FROM " & pstrData
        With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
            dblB = (!N * !SXY - !SX * !SY) / (!N * !SX2 - !SX ^ 2)
            dblA = (!SY / !N) - dblB * (!SX / !N)
        End With
        DForecast = dblA + dblB * dblNewX
    End Function

    As you see, the function calculates the linear regression for two fields, and uses that to compute a forecast for a new X. It's really just applying the formula you find in Excel help on any good Internet resource for basic stats. Given this table:

    dblX      dblY
    12      120
    13      135
    17      150
    10      108

    You can now use:

        ? DForecast(20, "tblData", "dblX", "dblY")

    Same result, much faster, can handle one million records... Excel function are great in Excel, but it's terribly inefficient to apply them to real-life database situations.

    Good luck!

    Author Comment

    Wow harfang-  Very nice work..  I will try to implement your code into my project and let you know how it goes.  Thank you all for your help.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now