Between Dates

Question

I have two tables.

Table 1 (tblPeriod) contains 3 fields - "Period", "PeriodStart", "PeriodEnd".

Though the period is usually a given month (eg. July 2024), the start date may differ than the actual date the month starts and ends. (accounting purposes).

Table 2 (tblSales) contains sales info which includes the sales date.

All sales will into a particular period (month).

So if the sale is dated July 8, 2024, it should be in the period July 2024.

I have built a query to capture this info but it it does not work (I knew it wouldn't)

I tried DLookup("Period","tblPeriod","InvoiceDate between PeriodStart and PeriodEnd).

I know my problem is the criteria.

Help me please.

Answer 1

This is perfect.

Thank you very much

Answer 2

Try

DLookup("Period","tblPeriod","PeriodStart<=#" & Format([InvoiceDate], "yyyy-mm-dd") & "# AND PeriodEnd>=#" & Format([InvoiceDate], "yyyy-mm-dd") & "#")