I would like a query or expression that will lookup a rate for a specific resource based on a date range.
Table 1 is TblDirectRates with the following fields:
Resource
FromDate
ToDate
Rate
Query 2 is ETCHRs
resoource
Datefield
Hrs
I would like a to add a field to the ETChrs Query that returns the rate from tbldirectrates depending on the date.
Here is what I have so far but it is just returning the first rate of the table.
rate: DLookUp("rate","TbldirectRates","Datefield">="[fromDate]" And "[datefield]"<="[toDate]" And "[resource]=[resource]")
any help would be great,
thanks
Table 1 is TblDirectRates with the following fields:
Resource
FromDate
ToDate
Rate
Query 2 is ETCHRs
resoource
Datefield
Hrs
I would like a to add a field to the ETChrs Query that returns the rate from tbldirectrates depending on the date.
Here is what I have so far but it is just returning the first rate of the table.
rate: DLookUp("rate","TbldirectRates","Datefield">="[fromDate]" And "[datefield]"<="[toDate]" And "[resource]=[resource]")
any help would be great,
thanks
Last edited: