MS Access - SQL Query for Max Date

Question

I am creating a schedule calendar which has been working great, but I want to adjust the SQL so that it only shows when the next job has to be done. I was thinking the best way to achieve this would be via the MAX() function, however when i run the code Access doesn't seem to like it.

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 Integer

On Error GoTo ErrorHandler

Text23 = Forms.frmPreventativeMenu.Form.CompanyName.Value

strQuery = "SELECT tblWMYReports.Company, tblWMYReports.Machine, MAX(tblWMYReports.NextDate), tblWMYReports.WMY " _
        & "FROM tblWMYReports " _
        & "WHERE (((tblWMYReports.Company)= " & Text23 & " ));"

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
                .Filter = "[NextDate]=" & MyArray(i, 0)
                '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

*Access doesn't seem to like it.* - that's not very helpful. The error message would be. Also see: [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, May 10 '18 at 14:54
Thank you for the link, have taken a read and it is extremely useful! — Shadyjunior, May 10 '18 at 15:23

score 1 · Accepted Answer · answered May 10 '18 at 14:51

1

You are going to aggregate one column with an aggregate function like Sum(), Max(), Count() or similar, then every other column that isn't being aggregated must show up in the SQL's GROUP BY clause:

strQuery = "SELECT tblWMYReports.Company, tblWMYReports.Machine, MAX(tblWMYReports.NextDate), tblWMYReports.WMY " _
        & "FROM tblWMYReports " _
        & "WHERE (((tblWMYReports.Company)= " & Text23 & " )) " _
        & "GROUP BY tblWMYReports.Company, tblWMYReports.Machine, tblWMYReports.WMY;"

I can't guarantee that is going to do what you want it to, since I'm not familiar with your data, code, or application, but it should get you through the error.

answered May 10 '18 at 14:51

JNevill

46,980
4
38
63

Thank you for your help. This was my initial concern but adding the group by still results in the same error. – Shadyjunior May 10 '18 at 14:59
1

Perhaps you need an alias on that field. It would help if you shared the error and the line on which it occurs. My guess is that `MAX(tblWMYReports.NextDate) as tblWMY` may help. – JNevill May 10 '18 at 15:09
I could kiss you right now >.< it's working perfectly now! I needed both the alias and "GROUP BY" clause. Thank you so much again. – Shadyjunior May 10 '18 at 15:17
I'm glad to hear that did the trick! I'll take a raincheck on the kiss though. – JNevill May 10 '18 at 15:58

score 1 · Answer 2 · answered May 10 '18 at 15:06

1

You must use a properly formatted string expression for the date value:

.Filter = "[NextDate] = #" & Format(MyArray(i, 0), "yyyy\/mm\/dd") & "#"

answered May 10 '18 at 15:06

Gustav

53,498
7
29
55

MS Access - SQL Query for Max Date

2 Answers2