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

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°)

use DSum:

DSum("YourField", "YourTable")

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

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,

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

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

> 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°)

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

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.