Dlookup value from 1 table with date ranges from diff table with a single date

I can't believe i can't get this simple thing to work! not very happy.



i have 2 tables.. 1 with bookings and 1 with a BonusPeriod range.



in the Bookings table there is a field called [ServiceDate]



in the bonus table i have a field called [Period] which is text

i also have 2 dates, [StartDate] And [EndDate]



i need to Dlookup the Bonus table to return the value of the period table based on where the [ServiceDate] falls.



i dont want to use VBA (i really need it to work within a query that inserts it's data into another table)



i've tried everything but had no joy :banghead:





Expr1: DLookUp("[Period]","BonusPeriods","[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")



Expr1: DLookUp("[Period]","BonusPeriods","[Staff_BookingsAndQuotes_Master].[ServiceDate]" Between "#[StartDate]#" And "#[EndDate]#")



Expr1: DLookUp("[Period]","BonusPeriods","(FormatDateTime([ServiceDate]),"yyyy-mm-dd") Between (FormatDateTime([BonusPeriods]![StartDate]),"yyyy-mm-dd") and (FormatDateTime([BonusPeriods]![EndDate],"yyyy-mm-dd"))))

Dlookup value from 1 table with date ranges from diff table with a single date