Query that acts similar to VLookup in excel

[bbeattie]

Could anyone advise me how to design a query to get a similar result as using VLookup in Excel.<br><br>In particular I have a table that holds transactions listed by date.&nbsp;&nbsp;In a second table, I have costs associated with particular dates.&nbsp;&nbsp;I want the query to create a table listing each transaction with a cost selected from the second table.&nbsp;&nbsp;If the date doesn't exist in the second table, I want the cost inserted from the record with the earlier existing date.<br><br>for example:<br><br>transaction_table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cost_table<br>Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;amount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cost<br>1/1/00&nbsp;&nbsp;&nbsp;&nbsp;25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/1/00&nbsp;&nbsp;&nbsp;&nbsp;$3<br>1/1/00&nbsp;&nbsp;&nbsp;&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/5/00&nbsp;&nbsp;&nbsp;&nbsp;$5<br>1/3/00&nbsp;&nbsp;&nbsp;&nbsp;10<br>1/4/00&nbsp;&nbsp;&nbsp;&nbsp;15<br>1/5/00&nbsp;&nbsp;&nbsp;&nbsp;9<br>1/5/00&nbsp;&nbsp;&nbsp;&nbsp;10<br><br><br>query result<br>date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;amount&nbsp;&nbsp;&nbsp;&nbsp;cost<br>1/1/00&nbsp;&nbsp;&nbsp;&nbsp;25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$3<br>1/1/00&nbsp;&nbsp;&nbsp;&nbsp;30&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$3<br>1/3/00&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$3<br>1/4/00&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$3<br>1/5/00&nbsp;&nbsp;&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$5<br>1/5/00&nbsp;&nbsp;&nbsp;&nbsp;10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;$5<br><br>Please help me.<br><br>&nbsp;<br><br><br>

 

[elizabeth]

Try the DLookup function. Formatting can be a bit tricky; if it doesn't work as expected it helps me to test it in the immediate window.

 

[Cornerstone]

Add the Cost field to the Transaction table<br>Sort the cost table in Descending order by the date field<br><br><br>in this example table1 is transactions<br>table2 is cost table<br>substitute your field names in for the dates and cost fields<br><br>Then run this procedure whenever you want to update that table. Please make a backup first.<br><br>Sub FillinCost()<br>Dim db As Database<br>Dim rst1 As Recordset<br>Dim rst2 As Recordset<br><br>Set db = CurrentDb<br>Set rst1 = db.OpenRecordset(&quot;table1&quot;, dbOpenDynaset)<br>Set rst2 = db.OpenRecordset(&quot;table2&quot<br><br>rst2.MoveFirst<br>rst1.MoveFirst<br><br><br>Do Until rst1.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;rst2.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;Do Until rst2.EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If rst2!ADate &lt;= rst1!TheDate Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst1.Edit<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst1!Cost = rst2!Cost<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst1.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst2.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;rst1.MoveNext<br>Loop<br>End Sub<br>