想学习如何自定义函数

[dtsdts]

Dim Rs As New ADODB.Recordset
Dim Rst As New ADODB.Recordset
Dim Cnn As New ADODB.Connection
Dim Y As Double
Dim StrSql As String
Dim Sql As String
Dim Ly As Long
Dim Xm As Integer
Set Cnn = CurrentProject.Connection


Sql = "SELECT DISTINCT 业务员, format(日期,'yyyymm') AS Rq FROM 业绩明细 ORDER BY 业务员, format(日期,'yyyymm')"

Rs.Open Sql, Cnn, adOpenKeyset, adLockOptimistic

Y = 0
Do While Not Rs.EOF

Ly = DSum("利润", "业绩明细", "业务员=" & Rs.Fields("业务员") & " and format(日期,'yyyymm')='" & Rs.Fields("Rq") & "'")

StrSql = "select top 1 * from 业绩明细 where 业务员=" & Rs.Fields("业务员") & " and format(日期,'yyyymm')='" & Rs.Fields("Rq") & "' order by 日期, 业绩编号 desc"

Rst.Open StrSql, Cnn, adOpenDynamic, adLockOptimistic

If Xm <> Rst.Fields("业务员") Then
Y = 0
End If
Do While Not Rst.EOF

Rst.Fields("月差额") = Ly - 2400 + Y

Rst.Update

If Rst.Fields("月差额") >= 0 Then
Y = 0
Else

Y = Rst.Fields("月差额")

End If

Rst.MoveNext

Loop
Rst.Close
Xm = Rs.Fields("业务员")
Rs.MoveNext

Loop

Set Rs = Nothing
Set Rst = Nothing
Set Cnn = Nothing
上面的代码是个按钮单击事件

请问：
1。如何将该代码修改成函数？使其能在查询里使用！


2。能不能将
ly = DSum("利润", "业绩明细", "业务员=" & Rs.Fields("业务员") & " and format(日期,'yyyymm')='" & Rs.Fields("Rq") & "'")

修改成子查询，如：
ly = "select Sum(业绩明细.利润) AS 利润 from 业绩明细 where 业务员= " & Rs.Fields("业务员") & " and format(日期,'yyyymm')='" & Rs.Fields("Rq") & "'"

[OracleRoob]

1、改成函数返回哪些数据？

2、DSUM()直接返回一个数值如果用子查询的话，需要用记录集打开返回数值。

[dtsdts]

1.返回月差额
2.个人感觉还是子查询好，dsum性能比较差，应该如何才能返回数值

[dtsdts]

表"业绩明细"----字段（id、日期、客户、运输方式、业务员、利润）

公司规定每人每月应为公司创造2400利润，不足部分下月应该补上（即为下月期初差额），如果超过2400，则超出部分不累计到下月（也即作0处理--------老板够黑）

我的思路如下：
设期初差额为y，if (期初差额-2400)>0 then y=0 else y=期初差额-2400，月差额=期初差额-2400+y
按这个思路如何将上面的代码修改为函数

[ewang11]

之前看过你的示例,计算每月利润直接生成临时表更方便一些

[dtsdts]

自定义函数未定义错误


Public Function varba(lr As Currency)
Dim y As Double
Dim CX As QueryDefs
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Set cnn = CurrentProject.Connection
Set CX = CurrentDb.QueryDefs("a")
y = 0
rs.Open CX, cnn, adOpenKeyset, adLockOptimistic
Do While Not rs.EOF
varba = lr - 2400 + y
rs.MoveNext
If varba > 0 Then
y = 0
Else
y = varba
End If

Loop
rs.Close
Set rs = Nothing
Set cnn = Nothing
End Function

[OracleRoob]

函数语法没问题，中断代码，逐行调试。

[dtsdts]

Public Function varba(lr As Currency) As Currency

Dim y As Double
Dim CX As String
Dim rs As New ADODB.Recordset
Dim cnn As New ADODB.Connection

Set cnn = CurrentProject.Connection
CX = "Select * From 业绩汇总"


rs.Open CX, cnn, adOpenDynamic, adLockOptimistic
y = 0
Do While Not rs.EOF

varba = lr + y - 2400
rs.MoveNext
If varba > 0 Then
y = 0
Else
y = varba
End If



Loop

rs.Close
Set rs = Nothing
Set cnn = Nothing

End Function

更改成上面代码
但还是有问题，但varba出现<0时，下一条计算出现错误