如图,前3列为表中数据,第4列(红色数据)为想通过函数求和数据.
尝试做的,但不好用!
Function Total(Code As String, DatF As Date, Xj As Single, n As Integer) As Double
Dim rs As DAO.Recordset
Dim Sql As String
Dim Total As Double
Dim Count As Integer
Total = 0
Count = 0
Sql = "SELECT Top " & n + 1 & " Dat,Dm,Xj FROM usysA WHERE Dm= '" & Code & "' ORDER BY Dat DESC"
Set rs = CurrentDb.OpenRecordset(Sql)
Do While Not rs.EOF
Total = Total + rs.Fields(1)
Count = Count + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Function

用上面的代码做查询,得出的结果如图:

建议用区域for循环 不要用do while

Excel是之前n=5个数的和吗？
vba。。参数是什么意义？靠猜吗

没用数据库，直接excel里跑的

'n为第几个5个XJ1之和
Public Function Total(Code As String, n As Integer) As Double
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim Sql As String
Dim cnt As Integer
Dim strConn As String
Dim sum As Double
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES'"
cn.Open strConn
Sql = "select top " & n + 4 & " * "
Sql = Sql & " from [usysA$]"
Sql = Sql & " WHERE Dm1 = '" & Code & "' order by Dat1 asc"
rs.CursorLocation = adUseClient
rs.Open Sql, cn, adOpenKeyset, adLockPessimistic
If rs.RecordCount < 5 Then
Exit Function
End If
rs.MoveFirst
rs.MoveLast
cnt = 0
sum = 0
Do While Not rs.EOF
sum = CDec(sum) + CDec(rs.Fields("XJ1"))
rs.MovePrevious
cnt = cnt + 1
If cnt >= 5 Then
Exit Do
End If
Loop
Total = sum
End Function
字段名字自己改下

access版
'n为第几个5个XJ1之和
Public Function Total(Code As String, n As Integer) As Double
Dim rs As DAO.Recordset
'Dim cn As New ADODB.Connection
Dim Sql As String
Dim cnt As Integer
Dim strConn As String
Dim sum As Double
' strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties='Excel 12.0 Macro;HDR=YES'"
' cn.Open strConn
'
If n < 1 Then
Exit Function
End If
Sql = "select top " & n + 4 & " * "
Sql = Sql & " from [表1]"
Sql = Sql & " WHERE Dm1 = '" & Code & "' order by Dat1 asc"
'rs.CursorLocation = adUseClient
'rs.Open Sql, cn, adOpenKeyset, adLockPessimistic
Set rs = CurrentDb.OpenRecordset(Sql)
rs.MoveFirst
rs.MoveLast
If rs.RecordCount < 5 Then
Exit Function
End If
cnt = 0
sum = 0
Do While Not rs.EOF
sum = CDec(sum) + CDec(rs.Fields("XJ1"))
rs.MovePrevious
cnt = cnt + 1
If cnt >= 5 Then
Exit Do
End If
Loop
Total = sum
End Function

查询语句
select *,Total([Dm1],DCount("*","表1","[Dat1] <=
#" & [表1]![Dat1] & "#
" )-5) AS Tal from [表1]
效果如图