Solved DLookup Issue

[locchio]

I have a table 'tblRateHistory' that has a field 'Rate EDU' that I'd like to pull the data from using a date field named 'Effective' (from the same table).

In a query I have a table 'tblBillingLog' that I will use to evaluate if 'Effective' is between date fields [Period Start] and [Period End] from the tblBillingLog.

In my query I have a field with the following syntax:

RateLookup: DLookUp("Rate EDU","tblRateHistory","[Effective] Between #" & [Period Start] & "# AND #" & [Period End] & "#")

When I run it I get #Error in that field and when I click on the field I get the following error

Syntax error (missing operator) in query expression 'Rate EDU'.

What am I messing up??

Thanks!

 

[arnelgp]

put it inside a Square brackett:

DLookUp("[Rate EDU]", ...

you do this when the fieldname is Reserved keyword of Access or it has Space between them.

 

[theDBguy]

Hi. Welcome to AWF!

 

[plog]

Dlookups have no place in a query. The correct way to obtain data from related data sources is by using a JOIN.

So, tblRateHistory should be brought into your query, joined to the [Effective] field already in it by using the Query Designer to link [Effective] to [Period Start] and [Effective] to [Period End]. Then you must go into the SQL view and change the INNER JOIN clause so that it uses ≥ and ≤ instead of = on the tblRateHistory table.

 

[locchio]

put it inside a Square brackett:

DLookUp("[Rate EDU]", ...

you do this when the fieldname is Reserved keyword of Access or it has Space between them.

Thanks. I should know better. It worked... MORE COFFEE!

 

[locchio]

Hi. Welcome to AWF!

Thank you.

 

[locchio]

Dlookups have no place in a query. The correct way to obtain data from related data sources is by using a JOIN.

So, tblRateHistory should be brought into your query, joined to the [Effective] field already in it by using the Query Designer to link [Effective] to [Period Start] and [Effective] to [Period End]. Then you must go into the SQL view and change the INNER JOIN clause so that it uses ≥ and ≤ instead of = on the tblRateHistory table.

Brilliant! So simple, but my brain was not wrapping around how to pull the data. I have done similar to edit queries, but just never thought of using it for a range. Clever. Thanks!

 

[CJ_London]

Just curious- have you considered the situation where you have two (or more) effective dates between your period start and end dates?

or for that matter the rate that was effective at the period start which is subsequently updated before the period end with another effective date?

 

[locchio]

Just curious- have you considered the situation where you have two (or more) effective dates between your period start and end dates?

or for that matter the rate that was effective at the period start which is subsequently updated before the period end with another effective date?

It won't happen with the data I am using. One rate per zone per time frame.