Create an UDF which returns the latest price of item at certain date. And then use this function with today's date.
I myself use the function
Code:
Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
Dim dbs As Database
Dim rs As Recordset
Dim varQstr As String
' The function returns the value of RetField from table parTable
' where IdField equals IdCond and DateField is nearest past or equal to parDate.
' The parameter IdIsString must be True, when IdField has Text format
On Error GoTo Err_ValidValue
Set dbs = CurrentDb
varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
" WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
" And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
" ORDER BY [" & DateField & "] DESC"
Set rs = dbs.OpenRecordset(varQstr)
rs.AbsolutePosition = 0
ValidValue = rs.Fields(0).Value
Err_ValidValue:
rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing
End Function