Dlookup formula in query using BETWEEN

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cydney - 03 Jul 2006 21:28 GMT

I am using a query to look up an exchange rate (or conversion rate) from a
table of weekly date ranges. I need to find the associated currency exchange
rate for that week where the expense occurred ("[PERIOD_END_DATE]").

My query formula is below. I can't figure out why it doesn't work. Can you
help?

ConvRate:
DLookUp("[CONVERSION_RATE]","CurrencyConversion","MyData.[PERIOD_END_DATE]
Between [effective_start_date] And [effective_end_date]")

Signature

THX cs

Reply to this Message

Cydney - 03 Jul 2006 21:42 GMT

I also tried this query formula:
NEWConvRate: IIf([PERIOD_END_DATE] Between
DLookUp("[effective_start_date]","CurrencyConversion") And
DLookUp("[effective_end_date]","CurrencyConversion"),[CONVERSION_RATE],1)

...still didn't work.... =\

Signature

THX cs

> I am using a query to look up an exchange rate (or conversion rate) from a
> table of weekly date ranges. I need to find the associated currency exchange
[quoted text clipped - 6 lines]
> DLookUp("[CONVERSION_RATE]","CurrencyConversion","MyData.[PERIOD_END_DATE]
> Between [effective_start_date] And [effective_end_date]")

Reply to this Message

Gary Walter - 03 Jul 2006 23:27 GMT

"Cydney"wrote:

>I am using a query to look up an exchange rate (or conversion rate) from a
> table of weekly date ranges. I need to find the associated currency
[quoted text clipped - 7 lines]
> DLookUp("[CONVERSION_RATE]","CurrencyConversion","MyData.[PERIOD_END_DATE]
> Between [effective_start_date] And [effective_end_date]")

it sounds like you have a table "CurrencyConversion"
with 3 fields

effective_start_date effective_end_date CONVERSION_RATE
6/4/2006 6/10/2006 1.6
6/11/2006 6/17/2006 1.8
6/18/2006 6/24/2006 1.5

and in a query based on table "MyData"
you want to determine the rate based on
MyData.PERIOD_END_DATE

If MyData is the only table in your query,
then the simplest solution would be just to
add the table "CurrencyConversion" to the
query without any joins (what they call a
Cartesian Join),

right-mouse click on this table in query designer,
choose "Properties"

set "Alias" to CC

double-click on CONVERSION_RATE
to add it as a new column in the grid

then, in "Criteria" row under PERIOD_END_DATE
column, type in (all one line)

>= CC.effective_start_date AND < CC.effective_end_date + 1

if you have more than one table in the original query,
then your domain function will need to separate out
the "domain stuff" from the "query stuff" in the where
portion of the domain function, i.e.,

the 2 effective date fields are part of the CurrencyConversion
domain, but PERIOD_END_DATE comes from "outside of
that domain"

ConvRate:
DLookUp("[CONVERSION_RATE]","CurrencyConversion",
"[effective_start_date] <= # " & MyData.[PERIOD_END_DATE]
& "# AND [effective_end_date] + 1 > #"
& MyData.[PERIOD_END_DATE]
& "#")

Reply to this Message

Gary Walter - 03 Jul 2006 23:38 GMT

if your PERIOD_END_DATE has no time portion,
I suppose you could also use

ConvRate:
DLookUp("[CONVERSION_RATE]","CurrencyConversion",
"#" & MyData.[PERIOD_END_DATE]
& "# BETWEEN [effective_start_date AND [effective_end_date]")

the important point being that fields within the domain
go inside the quotes, and field(s) outside of domain get
tacked on outside the quotes with ampersands (&),
with any needed delimiters inside the quotes.

"Gary Walter"wrote>

> "Cydney"wrote:
>>I am using a query to look up an exchange rate (or conversion rate) from a
[quoted text clipped - 56 lines]
> & MyData.[PERIOD_END_DATE]
> & "#")

Reply to this Message

Cydney - 05 Jul 2006 18:00 GMT

This one gave me an error "UNKNOWN" (... real descriptive .. =\ )
I looked it over carefully and can't seem to see what the problem would be.
I did include a bracket after "effective_start_date" as I think you might
have inadvertently left that out. Other than that, I couldn't see the problem.

Signature

THX cs

> if your PERIOD_END_DATE has no time portion,
> I suppose you could also use
[quoted text clipped - 70 lines]
> > & MyData.[PERIOD_END_DATE]
> > & "#")

Reply to this Message

Cydney - 05 Jul 2006 18:13 GMT

Because I have multiple data sets joined to MyData, I used your second
suggestion. It seems to be working. But I don't understand why we can't use
"Between" for that function.

Signature

THX cs

> "Cydney"wrote:
> >I am using a query to look up an exchange rate (or conversion rate) from a
[quoted text clipped - 55 lines]
> & MyData.[PERIOD_END_DATE]
> & "#")

Reply to this Message

Gary Walter - 06 Jul 2006 13:54 GMT

"Cydney"wrote:

> Because I have multiple data sets joined to MyData, I used your second
> suggestion. It seems to be working. But I don't understand why we can't
> use
> "Between" for that function.

<snip>

>> > ConvRate:
>> > DLookUp("[CONVERSION_RATE]","CurrencyConversion","MyData.[PERIOD_END_DATE]
>> > Between [effective_start_date] And [effective_end_date]")

It looks like in Access Help that the expr before
BETWEEN must identify "the field that contains
the data you want to evaluate":

***quote***
Between...And Operator

Determines whether the value of an expression
falls within a specified range of values.

Syntax

expr [Not] Between value1 And value2

The Between...And operator syntax has these parts:

Part Description
expr Expression identifying the field that
contains the data you want to evaluate.

value1,
value2 Expressions against which you
want to evaluate expr.

***unquote***

I had not thought about this before,
but I guess you proved it...

In your case, "the field" before the BETWEEN
needed to be a field within the domain of
"CurrencyConversion," not [Period_End_Date]
which was outside the domain. It just couldn't
do what you wanted, and I apologize for suggesting
it might w/o testing.

As you found, the only way was to break it out:

ConvRate:
DLookUp("[CONVERSION_RATE]","CurrencyConversion",
"[effective_start_date] <= # " & MyData.[PERIOD_END_DATE]
& "# AND [effective_end_date] + 1 > #"
& MyData.[PERIOD_END_DATE] & "#")

Looking back, I don't recall starting the "Where clause"
of an aggregate domain function with anything other
than a field from the domain.

This IS different than a WHERE clause in a SQL,
in fact, I believe I have read posts here that stated
reversing that "order" will sometimes improve efficiency
of a query.

Apologies again for suggesting it w/o testing.

Reply to this Message