DLookUp function to compare date?

[calvinle]

Hi,

I have 2 tables, Table 1 that log the amount of purchase at x date.
Table 2 log the price at x date.

I have 2 tables.

Table 1:
[ID] - [Date] - [Name] - [Quantity]
1 - 1JAN2017 - APPLE - 5
2 - 31JAN2017 - APPLE - 6
3 - 28FEB2017 - APPLE - 5

Table 2:
[ID] - [Date] - [Price]
1 - 31DEC2016 - 2$
2 - 2JAN2017 - 3$
3 - 28JAN2017 - 4$
4 - 28FEB2017 - 5$

If they select the record 3 in Table 1, it will populate in a control, the price at record 4 from Table 2.
If they select the record 2 in Table 1, it will populate in a control, the price at record 3 from Table 2.

Basically, the lookup formula should be "equal or after" a date.

Can anyone help me to create such formula or code?

Thanks

 

[MarkK]

You can filter by the date you want to test for, then sort by date, and then pick the first record. The SQL below is basically the solution, but encapsulated in a function I would do something like...

Function GetPrice(d as date) as Currency
   Const SQL As string = _
      "SELECT TOP 1 Price " & _
      "FROM Table2 " & _
      "WHERE [Date] <= prmDate " & _
      "ORDER BY [Date] Desc;"

   With CurrentDb.CreateQueryDef("", SQL)
      .Parameters("prmDate") = d
      With .OpenRecordset
         If Not .eof then GetPrice = .Fields("Price")
         .Close
      End With
      .Close
   End With
End Function

Do you see what's happening there?

 

[calvinle]

Hi,

Sorry, I have missed the info that I need to populate to a control in order to use it to Link Master Child subform.

See attachment.