CODEWITHSUNDEEP
sqlms-access
Shadyjunior
Shadyjunior

Reputation: 457

MS Access - SQL Query for Max Date

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

Upvotes: 0

Views: 896

Answers (2)

Gustav
Gustav

Reputation: 55806

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

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

Upvotes: 1

JNevill
JNevill

Reputation: 50019

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.

Upvotes: 1

Related Questions