Search code examples
ms-accesslookuplookup-tables

DLookup based on date ranges

I'm an ms access novice and i'm in the process of building a simple relational database. I have two tables, one containing a date field and another table containing a range for the dates and the value i want to return based on the date range. In excel i would use the LOOKUP function but in access, the only options seems to be to use the "DLookup" function.

enter image description here

the destination table has a column with the dates. So far, the expression that i've got which keeps returning an error is 

DLookup(DLookUp([KR_DB]![DPL_TargetMonth],[SeasonFY],[KR_DB]![DPL_TargetMonth] Between [SeasonFY]![SeasonDateFrom] And [SeasonFY]![SeasonDateTo])

I don't know how to write the proper syntax for this.

Solution

  • Saving the SeasonFY ID as foreign key in KR_DB would be the relational approach. However, DLookup could be like:

    SELECT KR_DB.*, 
DLookup("Season", "SeasonFY", "#" & [DPL_TargetMonth] & "# BETWEEN SeasonDateFrom AND SeasonDateTo") AS Season
FROM KR_DB;