Solved DLookup Issue (1 Viewer)

locchio

New member
Local time
Today, 03:58
Joined
Jul 23, 2021
Messages
9
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:58
Joined
May 7, 2009
Messages
19,169
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

I’m here to help
Staff member
Local time
Today, 00:58
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!
 

plog

Banishment Pending
Local time
Today, 02:58
Joined
May 11, 2011
Messages
11,613
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

New member
Local time
Today, 03:58
Joined
Jul 23, 2021
Messages
9
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

New member
Local time
Today, 03:58
Joined
Jul 23, 2021
Messages
9
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

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2013
Messages
16,553
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

New member
Local time
Today, 03:58
Joined
Jul 23, 2021
Messages
9
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.
 

Users who are viewing this thread

Top Bottom