Dlookup formula in query using BETWEEN

C

Cydney

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

Cydney

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...
 
C

Cydney

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...
 
C

Cydney

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

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

Invalid syntax in dlookup 4
Dlookup in query 1
Multi level groupby clause is not allowed in a subquery 3
Query DLookup calculation results in text not number 3
DSum in Query 3
using dlookup in a query 2
Using results of DLookup in report 6
Multiple Dlookup in the same query ?? 1

Top