Re: Writing Custom Aggregate Functions

Yes: you can OpenRecordset(), and loop through the records building up the output string.

Some examples:
- Return a concatenated list of sub-record values:
http://www.mvps.org/access/modules/mdl0004.htm

- ECount() - an extended DCount():
http://allenbrowne.com/ser-66.html

- EAvg() - an extended DAvg():
http://allenbrowne.com/ser-68.html

A basic recordset example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike C" <Mike C@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E7E8583C-61A6-4055-A5DB-20B605E1F629@xxxxxxxxxxxxxxxx
Hello,

Is it possible in Access to write your own custom aggregate functions?

What I am trying to do is write a function that will take a column of state
abbreviations and output the column as a string containing the long names of
the states separated by commas. I want to be able to put the function in a
textbox in the group footer of my report using the syntax:

=GenerateStateNames([State])

This is the exact same way that Sum() would behave on a column when used in
a footer. However, I have been unable to find out a way to code the function
so that it accepts [State] as a collection of values. I am able to pass it
as string and it will return the first value in the column, but for all
intents and purposes that is completely useless.

If anyone knows how I might accomplish this, it would be a great help.

Thanks,

Mike

.

Relevant Pages

  • Re: Getting A Group Footer from Detail Records??
    ... the new Report View. ... Add an unbound text box named txtResult to the Group Footer. ... Private mstrList As String ... Add this to the Format event of the group footer: ...
    (microsoft.public.access.reports)
  • Re: Incorrect Data on Last Page
    ... detail section of the report is a subreport that has a group on StockNum. ... Detail_Format event and then printing that string in the subreport footer. ... report's 1st group footer is the serial # string of the last group footer ... 1st group footer prints ...
    (microsoft.public.access.reports)
  • Re: Problem with list.insert
    ... Python docs which gives examples like these, ... MY EXPEPECTED STRING IS: ... print "OUTPUT STRING IS" ...
    (comp.lang.python)
  • Writing Custom Aggregate Functions
    ... Is it possible in Access to write your own custom aggregate functions? ... textbox in the group footer of my report using the syntax: ... This is the exact same way that Sumwould behave on a column when used in ... as string and it will return the first value in the column, ...
    (microsoft.public.access.reports)
  • Getting A Group Footer from Detail Records??
    ... I have a report which will have up to 4 detail lines. ... I want to have one long string with each of these seperated by a " / ... ", for the group footer. ... the events in the "On Format" of the details section, ...
    (microsoft.public.access.reports)
Quantcast