Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 998
  • Last Modified:

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

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?

Regards,
0
JoshinHtown
Asked:
JoshinHtown
1 Solution
 
harfangCommented:
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?
(°v°)
0
 
mbizupCommented:
use DSum:

DSum("YourField", "YourTable")

You can also specify criteria in a third optional argument.  This works on columns, not rows.
0
 
JoshinHtownAuthor Commented:
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.

Regards,
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
grendel777Commented:
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:
http://msdn2.microsoft.com/en-us/library/aa172193(office.11).aspx

Hope this helps,
Russell
0
 
JoshinHtownAuthor Commented:
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
0
 
harfangCommented:
> 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:

    =Nz([OctoberVal])+Nz([NovemberVal])

And you can also use:

    =XLSum([OctoberVal],[NovemberVal])

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...

(°v°)
0
 
JoshinHtownAuthor Commented:
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
0
 
harfangCommented:
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))
     170.25

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:

tblData
======
dblX      dblY
12      120
13      135
17      150
10      108

You can now use:

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

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!
(°v°)
0
 
JoshinHtownAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now