Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    If I define a Public Function in an Access VBA module, then I can use that function to manipulate fields that are selected in a SELECT statement. I wonder if I can define such a function to be used as an aggregate [color blue]user[/color] function (i.e. similar to SUM, AVERAGE)

    The idea is the following: suppose I have table which for several project contains fields (CashFlowDate, Amount) that describe the cashflow related to that project. If I could define a user function XIRR then I could in a single statement calculate the XIRR for several project as:

    SELECT ProjectName, XIRR(CashFlowDate, Amount) as IRR
    FROM tblProjects
    GROUP by ProjectName


    Probably wishfull thinking?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Have you tried it? Most existing VBA functions functions can be used in queries, although complex statements can get problematic. Another possibility would be to create a class module to create a recordset that gives you the result, but that's a concept I find hard to get my arms around most of the time.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    NO, I didn't try yet. I went for the VBA class approach: read a recordset, convert to an array, do the XIRR calculation. It just occurred to me that there could be a more elegant approach. I'll give it a try.

  4. #4
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    It won't work: as soon as I use a public user function, I get an SQL error message.

    [color Blue]SELECT ProjName,mySum(Amount) FROM tblProj GROUP BY ProjName[/color]

    where mySum is a Public function defined in an Access VBA module

    produces:

    You tried to execute a query that does not include the specified expression 'mySum(Amount)' as part of an aggregate function.

  5. #5
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    A corrected syntax will work. Try something like:

    SELECT ProjName,mySum(Amount) as myNumber FROM tblProj GROUP BY ProjName, mySum(Amount);

    Worked for me.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Of course that will work, that's what the error message says. The point is: I only want to group by Project not by Project and mySum.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Do you need the Group By at all?

    Code:
    SELECT ProjectName, XIRR(CashFlowDate, Amount) as IRR
    FROM tblProjects
    You are selecting from, tblProjects, so presumably there is only one record per Project anyway.

    Are CashFlowDate and Amount fields in tblProjects?
    Regards
    John



  8. #8
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rvWoody View Post
    Of course that will work, that's what the error message says. The point is: I only want to group by Project not by Project and mySum.
    Why don't you just build a query and test it. Then you can look at the SQL for the query and put it in code, if that is your objective.

    I must admit, the problem and objective are not clear to me.

  9. #9
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    In posting the original question, I simplified the problem to its bare essentials. By using some unfortunate names or wording I seem to confuse everybody, for which I apologize.
    So let's rephrase the question.

    The goal of the problem is to calculate for each project an internal rate of return. I essentially have two tables:

    tblProjects: contains fields like ProjectID and ProjectName (one row per project)
    tblCashFlows: contains fields ProjectID, CashDate and Amount. (more than one row per project, describes expenses and revenues for the projects)

    One way of doing it is joining the two tables and throwing the result into EXCEL which has a native XIRR function (albeit in an add-on Analysis Pack)

    SELECT ProjectName, CashDate, Amount
    FROM tblProjects, tblCashFlows
    WHERE tblProjects.ProjectID=tblCashFlows.ProjectID


    That supposes to automate EXCEL, with the further difficulty of getting the results back into an Access Report.

    The other way of doing it, is to write an Access class or module that executes that query, analyzes the resulting record set and passes the group of records that
    belongs to a same project to a user written XIRR function that performs the calculation. It avoids calling upon EXCEL at the expense of writing the function yourself.
    This is the approach I have taken today.

    So the XIRR function is [color blue]a function that acts on a group of records[/color], just like Access' aggregate functions (COUNT,AVG,...). It then occurred to me that it would be
    nice if I could use my function in just the same way, directly in an SQL query, thereby avoiding writing the class or module:

    SELECT ProjectName, XIRR(CashDate,Amount) as IRR
    FROM tblProjects, tblCashFlows
    WHERE tblProjects.ProjectID=tblCashFlows.ProjectID
    GROUP BY ProjectName


    This should produce one row per project with its name and it (financial) return.

    However, SQL will not let me do it.

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by rvWoody View Post
    So the XIRR function is a function that acts on a group of records, just like Access' aggregate functions (COUNT,AVG,...). It then occurred to me that it would be
    nice if I could use my function in just the same way, directly in an SQL query, thereby avoiding writing the class or module:

    SELECT ProjectName, XIRR(CashDate,Amount) as IRR
    FROM tblProjects, tblCashFlows
    WHERE tblProjects.ProjectID=tblCashFlows.ProjectID
    GROUP BY ProjectName
    But XIRR(CashDate,Amount) does not act on a group of records, it acts on two values from tblCashFlows.
    So you will get a different answer for each record in tblCashFlows.


    So I think, (and I don't know anything about IRR) that you need to you rewrite the XIRR function so that you can use this SQL
    Code:
    SELECT ProjectName, XIRR(ProjectID) as IRR 
    FROM tblProjects
    This function would then have the job of retrieving the relevant records from tblCashFlows and using them to work out the IRR for the relevant project.
    Regards
    John



  11. #11
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by John Hutchison View Post
    But XIRR(CashDate,Amount) does not act on a group of records, it acts on two values from tblCashFlows.
    So you will get a different answer for each record in tblCashFlows.
    That's true John. In the same way if you use SUM(Amount) it also acts apparently only on a field, but under the cover of SQL the GROUP clause forces it to act on all selected records. So SQL must somewhere retrieve and bundle the records. I dreamed of something similar for a User defined function.


    Quote Originally Posted by John Hutchison View Post
    So I think, (and I don't know anything about IRR) that you need to you rewrite the XIRR function so that you can use this SQL
    Code:
    SELECT ProjectName, XIRR(ProjectID) as IRR 
    FROM tblProjects
    This function would then have the job of retrieving the relevant records from tblCashFlows and using them to work out the IRR for the relevant project.
    This solution is a step in the right direction. It repacks the code to retrieve the records, which I previously wrote in a class module, into a function. But I still have to retrieve the records myself. This is probably as far as one can get.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •