Simple query

[Basil]

Hi,

I recently had a query that hasn't seen any response, so I
thought I'd go back to basics on it:

If you do a query (grouped) that has a field selecting the
maximum date from a date field (where multiple records fit
the grouping), do you know how I can get the data from
another field (eg location) within the same record as the
maximum date?

I have tried sorting by date and then choosing LAST
location, but this doesn't always work (it does sometimes,
but not always... very weird!).

Does anybody have any ideas, any help at all would be most
appreciated.

Thanks,

Basil

 

[Allen Browne]

This article explains for options:
http://www.mvps.org/access/queries/qry0020.htm

What I use is a replacement for DLookup() that allows you to specify a sort
order, so you can get field specifying ordering on another field:

Function ELookup(Expr As String, Domain As String, Optional Criteria,
Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Author: Allen Browne. (e-mail address removed)
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

'Build the SQL string.
strSQL = "SELECT TOP 1 " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
strSQL = strSQL & " ORDER BY " & OrderClause
End If
strSQL = strSQL & ";"

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
Resume Exit_ELookup
End Function