Get most recent record and use an attribute

[mcupito]

Hello, all.

So, I have a Table that consists of a Date and a Value. When the date is the most recent, I'd like to get the NetAssetValue associated with that date, AND use it to multiply to a field from a different table in a query.

I have been unsuccessful in using DMax or Sort Descending (to get the most recent date), and thus I am stuck asking this question.



I, essentially, would like to replace the NAV_Date with the most recent date, thus, it would return the appropriate NetAssetValue. As seen in the image, when the NAV_Date is correct (9/30/2013) it used the correct NetAssetValue ($2,500.00).

Anyone have any ideas?

Thanks
[IMGNOTHUMB]http://bytes.com/attachments/attachment/7313d1384536993/award.jpg[/IMGNOTHUMB]

[Seth Schrock]

You can run a query that gets the closest date and then do a dlookup to get the NetAssetValue. Your query would be something like

SELECT * FROM AwardsByYearRptQry
WHERE DateDiff("d", NAV_Date, Date()) = (
   SELECT MIN(DateDiff("d", NAV_Date, Date())) 
   FROM AwardsByYearRptQry 
)

In this case, this query would return the top four records. You would then just have to figure out which of the four you want returned in your DLookup.

[mcupito]

Thanks for your reply. What does that Date function do? Also, it returned some records with the most recent NAV_Date, however I am still unsure of how to correlate that data to the original query to use the most recent NetAssetValue?

[Seth Schrock]

The Date() function returns the current date. Unless you have a time stamp or a serial number that can tell you which one was entered last there is no other sure way to know which one is the most recent. In VBA there is a LastModified property to the recordset object, but unless you can guaranty that no one can modify existing records, then you can't be sure that it will return the most recent record.

[mcupito]

So I think I found a solution. I created another query that merely gets Max NAV_Date and Last NetAssetValue.. I used that query in the main query and used only the NetAssetValue from my "RecentNAV" query and it seems to be working..

Thanks all.

[Seth Schrock]

Glad you got it to work.

[mcupito]

Thanks for your help, Seth.

[NeoPa]

NB. If you want the current date with the current time element included also, use Now() instead of Date().

[Seth Schrock]

The Now() function wouldn't help if the field didn't have a time element in it though, right?

[NeoPa]

Not really. No.

If both the setting and the checking are done with Now() instead of Date() though, it all works more as required.