Public Function k(strCodeID As String, lngY As Long, lngM As Long) As Long
Dim strSQL As String
Dim rs As Object
Dim cn As Object
Set cn = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
'将材料代码和日期代入到查询条件中去
strSQL = "SELECT 发货开票表.型号, Sum(([发货]-[开票])) AS 库存 " & _
"FROM 发货开票表 " & _
"WHERE 发货开票表.日期 < #" & lngM & "/1" & "/" & lngY & "# And 型号 ='" &strCodeID & "' " & _
"GROUP BY 发货开票表.型号;"
rs.Open strSQL, cn, 1
If rs.EOF = True Then Exit Function
k = rs("库存")
'MsgBox k
rs.Close
Set rs = Nothing
Set cn = NothingEnd Function--------------------------------------------
WHERE 发货开票表.日期 < #" & lngM & "/1" & "/" & lngY & "# And 型号 ='" &strCodeID & "' " & _
"GROUP BY 发货开票表.型号;"
rs.Open strSQL, cn, 1
If rs.EOF = True Then Exit Function
上面的代码不太明白,能解释一下吗?
Dim strSQL As String
Dim rs As Object
Dim cn As Object
Set cn = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")
'将材料代码和日期代入到查询条件中去
strSQL = "SELECT 发货开票表.型号, Sum(([发货]-[开票])) AS 库存 " & _
"FROM 发货开票表 " & _
"WHERE 发货开票表.日期 < #" & lngM & "/1" & "/" & lngY & "# And 型号 ='" &strCodeID & "' " & _
"GROUP BY 发货开票表.型号;"
rs.Open strSQL, cn, 1
If rs.EOF = True Then Exit Function
k = rs("库存")
'MsgBox k
rs.Close
Set rs = Nothing
Set cn = NothingEnd Function--------------------------------------------
WHERE 发货开票表.日期 < #" & lngM & "/1" & "/" & lngY & "# And 型号 ='" &strCodeID & "' " & _
"GROUP BY 发货开票表.型号;"
rs.Open strSQL, cn, 1
If rs.EOF = True Then Exit Function
上面的代码不太明白,能解释一下吗?
"GROUP BY 发货开票表.型号;"
.........sql的条件,前面日期小于lngY 年lngM月1号,后面要求“型号”为值参数strCodeID,并且将结果按“型号”分组统计
rs.Open strSQL, cn, 1
.............打开上面的记录集
If rs.EOF = True Then Exit Function .........如果没有满足条件的记录,则结束
这样做是不是有循环的? 记录多了会不会慢呢?SELECT [发货开票表].[型号], Year([日期]) AS 年份, Month([日期]) AS 月份, k([型号],Year([日期]),Month([日期])) AS 上月结余, Sum([发货开票表].[发货]) AS 本月发货, Sum([发货开票表].[开票]) AS 本月开票
FROM 发货开票表
GROUP BY [发货开票表].[型号], Year([日期]), Month([日期]), k([型号],Year([日期]),Month([日期]))
ORDER BY [发货开票表].[型号], Year([日期]), Month([日期]);