Dlookup and latest date

[Cafe2UEMP]

Hi

I have a table which contains product prices. These vary (increase/decrease) based on a date which becomes a new record in the table.


I want to find a price based on the price in the table based on the date entered in a form.

I am trying to use a dlookup function with a date criteria but am a bit lost on this.

Would appreciate a bit of help.

Thanks

David

[June7]

Nevermind, not reliable code.

Really should save the record ID from Products table into the order record.

[ArviLaanemets]

Here is an UDF I use to get such values:

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