! DAO Equivalent of DMax !

[Wembly]

Hi,

In recent times I have been doing a lot of DMax's (along
with DCount's and DLookup's) and later realised their
inefficiencies and inadequacies. I then moved on to DAO
and am starting to realise its benefits.

However, I'm in the process of converting old 'D...'
codes to DAO, and am having some difficulties in finding
an equivalent for the DMax.

For example, if I want to find the latest date of from a
subset of records that have the same ID (a foreign key),
how would I write it? E.g. my recordset has 100 rows, of
which 10 will be of relevance (e.g. ID = 6), and of those
10 records I want the one that has the latest date from a
dateField.

This is what I have currently written.

rs.FindLast "[dateField] > " ??? & "[ID] = " & PrimaryKey

The primary key changes all the time (depending on what
the user wants to see on the form) and is defined as an
integer. What should the ??? be?

Thanks.

 

[Marshall Barton]

In recent times I have been doing a lot of DMax's (along
with DCount's and DLookup's) and later realised their
inefficiencies and inadequacies. I then moved on to DAO
and am starting to realise its benefits.

However, I'm in the process of converting old 'D...'
codes to DAO, and am having some difficulties in finding
an equivalent for the DMax.

For example, if I want to find the latest date of from a
subset of records that have the same ID (a foreign key),
how would I write it? E.g. my recordset has 100 rows, of
which 10 will be of relevance (e.g. ID = 6), and of those
10 records I want the one that has the latest date from a
dateField.

This is what I have currently written.

rs.FindLast "[dateField] > " ??? & "[ID] = " & PrimaryKey

The primary key changes all the time (depending on what
the user wants to see on the form) and is defined as an
integer. What should the ??? be?

Generally, if your recordset is going to be used this way,
then it should have been sorted by ID and dateField. If you
can do that (without creating a conflict with your other
uses of the recordset), then the FindLast will work.

Usually, the easiest way to sort the recordset is to use a
query. You didn't say what the recordset is based on so
this may not be appropriate, but maybe you could do somethng
like:
Set rs = db.OpenRecordset( _
"SELECT * FROM table " _
& "WHERE ? ? ? " _
& "ORDER BY ID ASC, dateField ASC"

Then, if you are not trying to exclude some dates, you could
find the latest record with:
rs.FindLast "[ID] = " & PrimaryKey

If you do want to limit the dates in some way that was not
taken care of in the query's WHERE clause, then the two
conditions on the FindLast would be written:
rs.FindLast "ID = " & PrimaryKey & " AND "
& "dateField > " & Format(dtvariable,"\#m\/d\/yyyy\#")