Having a hard time with Dmax

[Montez659]

Not even sure if that is what I need. What I do need is to return the record ID (transID) of the record where the date is the most recent (transDTStamp). So far, I can get it to return the date, but I can't figure out how to modify it to return the record ID.

a = DMax("transDTStamp", "tblTransactions")

 

[Norie]

You can't do it just with DMax.

In a query you would probably use a subquery.

Perhaps you could try something similar using DMax and DLookup.

 

[Montez659]

Gotcha. Put it into a query and then did a Dlookup on that query. Thanks!

 

[Norie]

Not quite, but that might work I suppose.

 

[Montez659]

It works, but I am interested in how you would do it. I kind of pieced this together from several different sources so who knows how stable it is (SQL isn't necessarily my strong point). This is what the Query looks like, then I did a DLookup on that query, as it would only contain one record.

SELECT tblTransactions.transID
FROM tblTransactions
WHERE (((tblTransactions.transDTStamp)=(select max([transDTStamp]) from tblTransactions)));

 

[Norie]

I'd use what I suggested DMax and DLookup.

DMax to get the date for the criteria of the DLookUp which returns the ID.

 

[Montez659]

Oh, ok, gotcha. It just clicked what you are saying. Essentially, return the date off of the DMax, and then use that as criteria for the DLookup. I like that way better, much smaller. Thanks!