当前位置: 代码迷 >> Access >> access兑现自定义聚合函数
  详细解决方案

access兑现自定义聚合函数

热度:5733   发布时间:2013-02-26 00:00:00.0
access实现自定义聚合函数
使用access对数据进行处理,遇到一个如何自定义聚合函数的问题:需要将字段进行连乘,类似于sum()的实现。


经过研究找到一种方法与大家分享:


access定义聚合函数需要使用vba函数:

Function sql(ByVal str As String) As String
    sql =  “select value from table where id = '”; + str +“'"
End Function

Function getResult(sql As String) As Double
    Static db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
    rst.MoveLast
    rst.MoveFirst
    Dim result As Double
    result = 1#
    Do While Not rst.EOF
        If rst.Fields(0).Value Then
            result = result * rst.Fields(0).Value
        End If
        rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
    getResult = result
End Function

sql语句改为:
select
*,
getResult(sql(id))
from
(
select distinct id
from table
)

  相关解决方案