Passing a list of records to a function in Access 2010

Question

Earlier I wrote a VBA function in Excel that takes a Range as input and does some math on it. I'm writing a query in Access right now and trying to do the equivalent with tables... I know you can do SELECT SUM(table.field) AS SumOfField already... how do I write my own method with similar input? Whenever I try it, it seems to be getting passed as a scalar, not a summable list I can for-each over.

EDIT: To clarify, I am trying to write a function like Sum() or StDev() that operates on an entire column. I have found plenty of examples for how to write a function that operates on a single element, but they're not what I need. What gets passed to the function when I type MyStDev(table.field), and how can I use it?

CLOSING EDIT: After further research, it does not appear that Access supports writing your own aggregate functions, despite teasing you with a consistent format for its built-ins. I hate magic functions. I will just export the data into something like excel where I can calculate to my heart's desire.

use `GROUP BY` to group on another field/multiple fields. Why are you trying to use a for loop on a sum? — serakfalcon, Jul 09 '14 at 15:48
What maths? Nearly all work on rcordsets should be done in queries, some more complex that others. — Fionnuala, Jul 09 '14 at 16:03
I'm calculating the standard deviation of one set of values using the other set as weightings. It's math that requires all the data in both columns to be present. I tried doing the math in the query itself but it doesn't do what I want if I use scalar functions like Sum(weights) in the same query as products like values*weights. I can't even use derived tables because access doesn't seem to support them. — nupanick, Jul 09 '14 at 17:24
Perhaps you could use VBA to "roll your own" domain aggregate function (similar to [DAvg()](http://office.microsoft.com/en-ca/access-help/davg-function-HA001228815.aspx)) that could perform your custom statistical calculation. There is an example of how to do that [here](http://stackoverflow.com/a/19631024/2144390). — Gord Thompson, Jul 09 '14 at 20:37

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

To compute standard deviations all you need is the values and then you can simply use Access StDev formula

E.g

SELECT StDev(VALUE) AS MyStdev
FROM TableA
where  Date>'12/31/2012'
GROUP BY ID
HAVING ID="AAPL"

Update: In that case, there are several ways to do it , one way I can think of is to write a function that accepts some parameter (typically id) and use that id to select the records from the tables (join tables if required ) and perform the necessary math

Public Function MyStdev(ID as integer) As double
 Dim rst As Recordset
 Dim strSQL As String
 Dim std as double
 strSQL = "<Your SQL statement goes here>    
 Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)        
If rst.EOF = False Or rst.BOF = False Then
 rst.MoveFirst
 std = rst!FieldName
End If
'Do something with your values and return 
 Set rst = Nothing
 MyStdev=std    
End Function

If you are still confused,There are several examples on this site , consider this one

edited May 23 '17 at 12:20

Community

1
1

answered Jul 09 '14 at 19:03

Ravi Yenugu

3,895
5
40
58

The reason I didn't mention standard deviations in my original question is because I knew I'd get this answer if I did. I'm doing a modified standard deviation formula, not the stock one, and therefore I really do need to know how to write my own aggregate functions. – nupanick Jul 09 '14 at 19:05
Hmm. I'll give it a shot. It really seems like I ought to just be able to pass in the data directly like I would to the stock StDev function, but then again this is VBA we're talking about. – nupanick Jul 09 '14 at 20:19
Since you are making a custom function it is a little tricky, once you have your function working then you can mostly treat it as regular function. – Ravi Yenugu Jul 09 '14 at 20:22
This feels like a step in the right direction, but I'm not sure what you mean by taking an ID number as input in your example. Shouldn't I be passing in the field I want to operate on? It also seems like I'm supposed to have an SQL query here, which I saw hinted at in other examples, but that also seems wrong since I'm calling this from within a SELECT statement already. – nupanick Jul 09 '14 at 20:42
@user3821202: I think Thinkingcap's idea is that you pass an ID to limit the values you want to work with, though he doesn't use it in his code. You may have a domain of many values, only some of which pertain to what you want to calc. Thus, you would filter, or at least run a conditional, on an ID. This may not be your case. If you calc the same field all the time, you wouldn't have to pass in a fieldname, but if not, you can pass it in as another argument, which would make the function much more flexible. – VBlades Jul 09 '14 at 21:24
I just want the input format to be consistent with stock stdev for my sanity. Since I'm already inside a query, I don't have to filter again, right? ...but then how do I read in the field that was passed? Can I just pass it as type Field or something? What's the equivalent of excel's Range objects here? – nupanick Jul 09 '14 at 21:30
You can use `SELECT This, That, MyCustomFunction(This) ...` Check out User Defined Functions (UDF). They are only available within MS Access – Fionnuala Jul 10 '14 at 01:11
@user3821202: Yes, if you have isolated the values you want to use in the query already, then no, you wouldn't have to pass a limiting element through. As for how to pass the field through, you can't pass objects through a query, so it would have to be a string with the name of the field. In your code which goes through the recordset, you can reference that field name. In this case, the Range analog for your function is a column in the query which your recordset is based on. It is a bit unintuitive because you are actually materializing the "range" in your recordset and looping through it. – VBlades Jul 10 '14 at 04:57
@Remou That's exactly what I want to do - but I can't find any examples of how to write an Access UDF for an aggregate-type function, the examples all do something that operates on a single element like string reversal or value checking. – nupanick Jul 10 '14 at 13:15
You can use a recordset in a UDF, and that is a list of values. You can pass the recordset as an SQL string, a table name, whatever works for you. See http://stackoverflow.com/a/93863/2548 or http://wiki.lessthandot.com/index.php/Aggregate_Median_(UDF) for some ideas. – Fionnuala Jul 10 '14 at 13:19

score 0 · Accepted Answer · answered Oct 01 '14 at 17:06

0

After further research I determined that what I'm trying to do is impossible, in that I want to do it by defining a new aggregate function in VBA. VBA does not allow this. For the curious, I ended up using a subquery to do the extra aggregation.

answered Oct 01 '14 at 17:06

nupanick

754
8
13

score 0 · Answer 3 · edited Jun 06 '18 at 19:27

With your table, if you create a CrossTab Query and put table.field as the VALUE then with this result you can pass all the field values to a ParramArray and use this in your self-created function.. Example

Public Function RStDev(ParamArray FieldValues()) As Variant
    Dim dblSum As Double, dblSumOfSq As Double
    Dim n As Long, varArg As Variant

    For Each varArg In FieldValues
        If IsNumeric(varArg) Then
            dblSum = dblSum + varArg
            dblSumOfSq = dblSumOfSq + varArg * varArg
            n = n + 1
        End If
    Next
    If n > 1 Then ' Variance/StDev applies if more than a single point
        RStDev = Sqr((n * dblSumOfSq - dblSum * dblSum) / (n * (n - 1)))
    Else
        RStDev = Null
    End If
End Function

Passing a list of records to a function in Access 2010

3 Answers3