I have an Assets_Trans file, which keeps a history, by date, of Asset assignments to sites. An asset can be assigned to a site for a time and then later, moved to a different site. Each assignment creates a separate Assets_Trans record.
When I want to show all assets assigned to a site, currently, the form shows all current and historical assets assigned to that site.
I need it to show only those assets currently assigned to the site. I have produced the following code in an attempt to restrict the display of assets to only the current ones, using ELookUp(), but, as written, it does not provide the needed restriction. I was assuming the query would find only the first (most current) Assets_trans record for an asset and attempt to match against it.
Code:
=ELookUp("[Site_ID]","Assets_Trans","[Site_ID] = " & [Sites].[ID],"[Assets_Trans].[Trans_Date] Desc")