Look up formula

[chavez_sea]

Hi,
I would like to make a formula in a query to look up whether a date falls within a range of beginning and ending date and return a week number from a table. Eg.

Week Number	Beginning Date	Ending Date
1	06-Jan-19	12-Jan-19
2	13-Jan-19	19-Jan-19
3	20-Jan-19	26-Jan-19

Thanks for any help.

[June7]

=DLookup("WeekNumber", "tablename", "#" & [datefield] & "# BETWEEN [BeginningDate] AND [EndingDate]")

[pbaldy]

In a query I'd use a non-equi join. If there are a lot of records, a DLookup() can really slow down a query.

[chavez_sea]

This is the query:
Wk: DLookUp("WeekNumber","TblWeek Numbers","#" & [datefield] & "# BETWEEN [BeginningDate] AND [EndingDate]")
Give me a box EnterParamater Value Datefield, I press OK to bypass and then a syntax error ##Between BETWEEN [BeginningDate] AND [EndingDate'
Please for your suggestions

[chavez_sea]

Thank you, However I am not familiar with non-equi join. WOuld you help me with this.

Thanks

[chavez_sea]

Ok I changed the datefield to the field I want it to return now it is giving me an #error

[June7]

The expression example I provided worked for me with my data. I don't know your data. Do your field names have spaces? If they do, must enclose in [ ] - [Week Number]

[chavez_sea]

Week Number	Beginning Date	Ending Date
1	06-Jan-19	12-Jan-19
2	13-Jan-19	19-Jan-19
3	20-Jan-19	26-Jan-19
4	27-Jan-19	02-Feb-19
5	03-Feb-19	09-Feb-19

[June7]

So modify the examples as appropriate. Use your actual table and field names with their spaces.

[pbaldy]

Thank you, However I am not familiar with non-equi join. WOuld you help me with this.

Thanks

To your existing query against the transaction data you'd add the bits in red(in SQL View), using your table/field names:

SELECT Field1, Field2..., PayrollTable.[Week Number]
FROM TransactionTableName TT LEFT JOIN PayrollTable PR ON TT.[DateFieldName] >= PR.[Beginning Date] AND TT.[DateFieldName] <= PR.[Ending Date]
WHERE...

Note you won't be able to use design view as it can't represent a non-equi join. The DLookup() will return the same result, but if the table is large will likely be much slower.