MS Access中使用MAX()函数获取下一次作业日期的SQL查询问题

嘿，我猜你遇到的报错应该是Access提示“试图执行的查询中不包含作为聚合函数一部分的特定表达式”这类信息吧？这是因为Access的Jet SQL对聚合查询有严格的语法要求：当你在SELECT中使用MAX()这类聚合函数时，所有没有被聚合的字段必须明确放在GROUP BY子句里。

原SQL的问题分析 #

你的原查询里选择了Company、Machine、MAX(NextDate)和WMY，但没有对前三个非聚合字段做分组。Access无法确定这些字段应该如何和聚合后的MAX(NextDate)对应，所以直接报错了。

修正后的SQL与VBA代码 #

根据你“仅显示下一次作业执行时间”的需求，我们需要给查询加上GROUP BY子句，同时给MAX()的结果起一个别名方便后续引用。另外还要注意如果Company字段是文本类型，WHERE条件里的参数需要加单引号（如果是数字类型就不用）。

修正后的VBA代码片段如下：

Public Sub LoadArray()
    'This sub loads an array with the relevant variables from a query
    Dim db As Database
    Dim rs As Recordset
    Dim rsFiltered As Recordset
    Dim strQuery As String
    Dim i As Integer
    Dim Text23 As Variant '建议改成Variant，避免类型不匹配问题
    
    On Error GoTo ErrorHandler
    
    '获取Company值，注意如果是文本类型，后续SQL要加单引号
    Text23 = Forms.frmPreventativeMenu.Form.CompanyName.Value
    
    '修正后的SQL：添加GROUP BY，给MAX结果起别名
    strQuery = "SELECT tblWMYReports.Company, tblWMYReports.Machine, " _
             & "MAX(tblWMYReports.NextDate) AS MaxNextDate, tblWMYReports.WMY " _
             & "FROM tblWMYReports " _
             & "WHERE tblWMYReports.Company = " & IIf(IsNumeric(Text23), Text23, "'" & Text23 & "'") & " " _
             & "GROUP BY tblWMYReports.Company, tblWMYReports.Machine, tblWMYReports.WMY;"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strQuery)
    
    With rs
        If Not rs.BOF And Not rs.EOF Then
            'Ensures the recordset contains records
            For i = 0 To UBound(MyArray)
                'Will loop through the array and use dates to filter down the query
                'It firsts checks that the second column has true for its visible property
                If MyArray(i, 1) = True Then
                    '注意这里要改成我们给MAX结果起的别名MaxNextDate
                    .Filter = "[MaxNextDate]=" & Format(MyArray(i, 0), "\#mm\/dd\/yyyy\#") '日期字段要加#包裹
                    'To filter you must open a secondary recordset and
                    'Use that as the basis for a query
                    'This makes sense as you are building a query on a query
                    Set rsFiltered = .OpenRecordset
                    
                    If Not rsFiltered.BOF And Not rsFiltered.EOF Then
                        'If the recordset is not empty then you are able
                        'to extract the text from the values provided
                        Do While Not rsFiltered.EOF = True
                            MyArray(i, 2) = MyArray(i, 2) & vbNewLine & DLookup("MachineName", "tblMachine", "MachineID=" & rsFiltered!Machine)
                            MyArray(i, 2) = MyArray(i, 2) & " - " & DLookup("WMY", "tblWMY", "ID=" & rsFiltered!WMY)
                            rsFiltered.MoveNext
                        Loop
                    End If
                End If
            Next i
        End If
        .Close
    End With

ExitSub:
    Set db = Nothing
    Set rs = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "There has been an error. Please reload the form.", , "Error"
    Resume ExitSub
End Sub

额外注意点 #

1. 日期字段过滤：在Access中过滤日期字段时，需要用#把日期值包裹起来，我在代码里加上了Format(MyArray(i, 0), "\#mm\/dd\/yyyy\#")来确保格式正确，避免日期过滤失败。
2. 变量类型：原代码里把Text23定义为Integer，但如果CompanyName是文本类型的话，会导致类型不匹配错误，建议改成Variant或者根据实际字段类型调整。
3. GROUP BY的范围：如果你的需求是“每个公司的下一次作业时间”，而不是每个Machine+WMY组合的，那可以把GROUP BY改成只包含Company，同时去掉SELECT里的Machine和WMY，根据你的实际业务需求调整即可。

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