如何创建数据库求和查询并获取记录集？求和结果异常排查

原代码问题分析 #

1. 你的SQL语句按CatalogID分组，返回的是每个课程编号对应的时长总和（多行记录），但代码直接取rst.Fields(1)只会读取第一行的求和结果，若你需要的是所有课程的总时长，逻辑完全不符。
2. 返回类型用Integer存在溢出风险：当课时总和超过32767时会报错，建议改用Long类型。
3. 未处理空记录集场景，若查询无结果直接读取字段会引发运行时错误。

修复方案 #

方案1：统计所有课程的总时长（不分组）

如果需要计算所有课程的课时总时长，修改SQL去掉分组逻辑，直接全局求和：

Function Cduration() As Long
    Dim SQL As String
    Dim dbs As Database, rst As Recordset

    SQL = "SELECT Sum(Lessons_tbl.LessonDuration) AS [Total Course Duration] " & _
            "FROM (ListModule_tbl INNER JOIN Course_tbl ON ListModule_tbl.[ModuleID] = Course_tbl.[ModuleID]) " & _
            "INNER JOIN Lessons_tbl ON Course_tbl.[CourseID] = Lessons_tbl.[CourseID];"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(SQL)
    
    ' 处理空结果，Nz确保空值返回0
    If Not rst.EOF Then
        Cduration = Nz(rst.Fields(0), 0)
    Else
        Cduration = 0
    End If
    
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

方案2：统计指定课程编号的时长（分组查询）

如果需要针对某个特定CatalogID统计时长，给函数添加参数并在SQL中增加过滤条件：

Function Cduration(ByVal targetCatalogID As String) As Long
    Dim SQL As String
    Dim dbs As Database, rst As Recordset

    SQL = "SELECT Sum(Lessons_tbl.LessonDuration) AS [Course Duration] " & _
            "FROM (ListModule_tbl INNER JOIN Course_tbl ON ListModule_tbl.[ModuleID] = Course_tbl.[ModuleID]) " & _
            "INNER JOIN Lessons_tbl ON Course_tbl.[CourseID] = Lessons_tbl.[CourseID] " & _
            "WHERE Course_tbl.CatalogID = '" & targetCatalogID & "' " & _
            "GROUP BY Course_tbl.CatalogID;"

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(SQL)
    
    If Not rst.EOF Then
        Cduration = Nz(rst.Fields(0), 0)
    Else
        Cduration = 0
    End If
    
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

内容的提问来源于stack exchange，提问作者teri bridges