asked on

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 Period field value 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

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"))))

Expr21: Nz(DLookUp("Period","bonusperiods","#" & "servicedate" & "# BETWEEN StartDate AND EndDate"),0)

Dale Fye

Try:

Expr21: Nz(DLookUp("Period","bonusperiods","[ServiceDate] BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#"),0)

Another consideration is what if the [StartDate] or [EndDate] on a record is NULL?

Benyaw616

ASKER

Hi Dale,

the bonusperiod table doesn't have any null fields, I've manually added the records myself (it's a reference table)

i've tried the sql code you mention above and it prompts me for the start and end date as a parameter and it also gives an error message.
Capture.JPG

Gustav Brock

You can use:

Select
Table1.*, Table2.*
From
Table1, Table2
Where
CDate([Period]) Between [StartDate] And [EndDate]

/gustav

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Benyaw616

ASKER

Thank you so much dale! that work perfectly!!

Benyaw616

ASKER

Hi Dale,

Is it possible for you to amend this code so it removes local date formats? i'm in the UK and it's applying the filter in the US format of mm/dd/yyyy instead of dd/mm/yyyy

it's a long time ago but i did change something similar to the number format and it removed this issue (i can't find it anywhere)

Gustav Brock

That would be:

"#" & Format([ServiceDate], "yyyy\/mm\/dd") & "# BETWEEN [StartDate] AND [EndDate]"

/gustav

Benyaw616

ASKER

Thank you Gustav!!!!!!!