dlookup or other solution? (1 Viewer)

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

There is no Resource field in Query2. What table is used in Query2?

Is Resource a text field?

rate: DLookUp("rate", "TbldirectRates", "#" & [Datefield] & "# BETWEEN [FromDate] AND [ToDate] And [Resource]='" & [enter resource] & "'")

Or try it this way too.
Is [resource] a number field?

theDBguy said:

Or try it this way too.

Code:Copy to clipboard

DLookup("rate","tblDirectRates","[DateField]>=#" & Format([fromDate],"yyyy-mm-dd") & "# And [DateField]<=#" & Format([toDate],"yyyy-mm-dd") & "# AND [resource]=" & [resource])

Is [resource] a number field?

Click to expand...

Resource is a text field.

I am getting an Enter Parameter Value on the FromDate and ToDate field.

June7 said:

There is no Resource field in Query2. What table is used in Query2?

rate: DLookUp("rate", "TbldirectRates", Datefield & " BETWEEN [fromDate] And [toDate] And [Resource]=" & [enter resource])

Click to expand...

sorry for the typo in the original, I edited now. GroupName should have been resource.

First, just for syntax simplicity, look up the "BETWEEN ... AND ..." operator, which lets you bracket dates.

Second, note that for a Domain Aggregate (you are using Domain Lookup, e.g.) the syntax is that the field should be enclosed in square brackets i.e. DLookup("[rate]", ...)

Third, note that your secondary filter is a tautology that will always be true. [Resource] will always be equal to [Resource]. However, if you qualified them both then this would make more sense. Your query 2 doesn't contain a [Resource] column so it can't come from there. I'm going to assume that one of the [Resource] references is from table 1 and the other is from a form?

EDIT: Later post reveals that GroupName should be Resource. However, the query ETCHrs doesn't tell us from where the data originates. In my example, I am going to call that table ETCSrc.

Fourth, you need to verify ahead of time that any date range you are going to look up is actually supported by your table of direct rates. I.e. ETCHrs can NEVER contain a date that is earlier than the earliest date or later than the latest date in the direct rates table.

Fifth, this actually doesn't need to be a DLookup because SQL syntax gives you an alternative called a sub-query.

http://allenbrowne.com/subquery-01.html

Your ETCHrs query might do something like this (approximation!!!!)


This should work as long as ETCHrs always picks a date covered by TblDirectRates. I have highlighted the sub-query for display purposes. Where the field names are unique between the two tables, you don't need to qualify them - but Resource is used twice so must be qualified where it is used.

Note: [ ] are required if names have spaces or special characters or are reserved words. Oddly, in domain aggregate, tablename will work without [ ] even if there are spaces.

rate: DLookUp("rate", "TbldirectRates", "#" & [Datefield] & "# BETWEEN [FromDate] AND [ToDate] And [Resource]='" & [resource] & "'")

@theDBGuy - have field and parameters concatenation backwards for the date criteria.

Nested query alternative might perform faster.

June7 said:

Note: [ ] are required if names have spaces or special characters or are reserved words. Oddly, in domain aggregate, tablename will work without [ ] even if there are spaces.

rate: DLookUp("rate", "TbldirectRates", "#" & [Datefield] & "# BETWEEN [FromDate] AND [ToDate] And [Resource]='" & [resource] & "'")

@theDBGuy - have field and parameters backwards for the date criteria.

Nested query alternative might perform faster.

Click to expand...

This works Great!!! Thank you everyone for the help. I might try get the query alternative to work since it will be faster.
thanks again

Hi. Glad to hear you got it sorted out. Sorry for the error. Good luck with your project.

June7, my point about the field in a domain aggregate is that it usually requires bracketing but it IS true that the table/query name does not.

Field reference works fine without [ ] as long as there are no spaces or special characters. Reserved word as name might be an issue. So not sure what you mean by 'usually'.

I've just always seen it with the brackets - and the domain of the aggregate NOT within brackets - in all documents from MS and FMSInc. If it works, fine.