Retrieve value based on multiple criteria

[Sydcomebak]

Given:
- a text box on a form "Text476" with the contents: =Format(Date(),"yyyy")
- a dropdown where the values of A, B, C, D, or E could be selected
- a table "Lease_Rates" Structured:

ID Lease_Year Class Rate
 1   2011       A   $200
 2   2011       B   $210
 3   2011       C   $220
 4   2011       D   $230
 5   2011       E   $240
 6   2012       A   $205
 7   2012       B   $215
 8   2012       C   $225
 9   2012       D   $235 
10   2012       E   $245

What code do I place in "Text478" to retrieve the correct Rate?

Thanks everyone! First DB to help my dad with his real-estate woes. Using Excel to keep track of 800+ homes *shudder.*

-Dave

 

[Kiwiman]

Howzit

You want a DLookup to get the results. Put it in the after update event of the control that is entered last (either the date or the class control)

me.Text478 = DLOOKUP("[Rate]","Lease_Rates","[Lease_Year]=" & me.Text476 & " AND [Class]='" & me.yourdropdownctrlname & "'")