Dlookup formula in query using BETWEEN

G

Guest

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]")
 
G

Guest

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.... =\
 
G

Gary Walter

"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
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]")
--
Click to expand...

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
Click to expand...

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]
& "#")
 
G

Gary Walter

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
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]")
--
Click to expand...

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
Click to expand...

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]
& "#")
Click to expand...
 
G

Guest

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.
--
THX cs


Gary Walter said:
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
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]")
--
Click to expand...

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
Click to expand...

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]
& "#")
Click to expand...
Click to expand...
 
G

Guest

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.
--
THX cs


Gary Walter said:
"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
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]")
--
Click to expand...

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
Click to expand...

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]
& "#")
Click to expand...
 
G

Gary Walter

"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.
--
ConvRate:
DLookUp("[CONVERSION_RATE]","CurrencyConversion","MyData.[PERIOD_END_DATE]
Between [effective_start_date] And [effective_end_date]")
--
Click to expand...
Click to expand...
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query DLookup calculation results in text not number 3
DSum in Query 3
Using results of DLookup in report 6
using dlookup in a query 2
Using SUMPRODUCT, 3 variables, 2 types of data in 1 column 3
Toggle box and its use in a formula 9
Can't query DateDiff 3
Date Range in Query using DateAdd 4

Top