Value值的计算

Public Function GetMyVal(tableName As String, _
                         UnGroupFiels As String, _
                         UnGroupValue As String, _
                         DsumValueFieldName As String, _
                         Optional WhereValue = 0, _
                         Optional OrderByStr As String = "") As Long
    Dim MyVal As Long
    Dim i As Long
    Dim Rs As ADODB.Recordset
    Dim AddValueBool As Boolean
    Dim StrSql As String
    
    AddValueBool = False
    StrSql = "select " & UnGroupFiels & " AS 组号," _
           & DsumValueFieldName & " from " _
           & tableName _
           & " where " & UnGroupFiels & "='" & UnGroupValue _
           & "' " & OrderByStr
    Set Rs = New ADODB.Recordset
    Rs.Open StrSql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    For i = 0 To Rs.RecordCount - 1
        If i = 0 Then MyVal = Rs(DsumValueFieldName) + MyVal
        If Nz(Rs(DsumValueFieldName), 0) = WhereValue Then AddValueBool = True
        If Nz(Rs(DsumValueFieldName), 0) <> WhereValue And AddValueBool = True Then
            MyVal = Rs(DsumValueFieldName) + MyVal
            AddValueBool = False
        End If
        Rs.MoveNext
    Next
    Rs.Close
    Set Rs = Nothing
    GetMyVal = MyVal
End Function

先建一个查询为：过渡查询1：SELECT DISTINCT 设备号, 端口号, Format([date],"yyyy\/mm\/dd") AS 日期

FROM Sheet1;

再建一查询：统计

SELECT 过渡查询1.设备号, 过渡查询1.端口号, 过渡查询1.日期, GetMyVal("sheet1","[设备号] & [端口号] & Format$(date,'yyyy-mm-dd')",[设备号] & [端口号] & Format$([日期],"yyyy/mm/dd"),"value",0,"order by date desc") AS MyVal
FROM 过渡查询1;