DLookup

[mike60smart]

Hi Everyone

I have a table that contains a list of Exchanges rates as shown.

In my continuous form which is based on a query I have a field named "ImportCurrency" which is populated with all
of the various Currencies.

I am trying to create a field in the query to return the FX Rate.

I tried the following but it returns #Error

Rate: DLookUp("FX Rate","tblCurrencyCalculations","[Currency1]='" & [ImportCurrency] & "'")

Any help appreciated.

 

[Minty]

You have a space in your field name, try putting square brackets around it;

Rate: DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [ImportCurrency] & "'")

 

[mike60smart]

Hi Minty

OK That worked just fine.

The example I uploaded was a test.

Why am I getting an error in the following:-

Rate: IIf([IncomingCurrency]="GBP",DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [IncomingCurrency] & "'",IIf([IncomingCurrency]="USD",DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [IncomingCurrency] & "'",""))))

 

[Minty]

I'm going to guess because you haven't specified a null return value for the second IIF()

Single test IIf(TestCondition, True, False)
Nested test IIf(TestCondition, True, (IIf(TestCondition, True)

Your First IIf() is correct - The second nested one has no false part.

However this solution doesn't scale, you really need to join the currencies rates as a second table, even if it's using the same base table.
Nesting DLookup() in a query is going to get painfully slow if you have any quantity of records.

 

[CJ_London]

looks to me like your first dlookup doesn't have a closing bracket

since the two dlookups are identical I don't see why you need an iif unless you are only interested in GBP and USD and want to ignore other currencies - in which case

Rate: IIf([IncomingCurrency] In ("GBP","USD"),DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [IncomingCurrency] & "'"))

 

[mike60smart]

Hi CJ

That works just great.

Many thanks

 

[plog]

It may be producing the right results but you're absolutely doing it wrong.

Dlookups have no place in queries. You need to JOIN tblCurrencyCalculations to the data source(s) in the query already.

 

[bastanu]

If you check out my post (#3) in your previous thread on this issue you will see that I suggested using an unbound control to dynamically set the first argument (field name) of the dLookup.

Lookup

Hi Everyone I have the following data in a Table named "tblCurrencyCalculations" CurrencyID Currency USD EURO AUD GBP CAD INR MXN JPY 1 USD 1 1.156454 0.730939 1.362368 0.796875 0.013366 0.048532 0.008972 2 EURO 0.864712 1 0.632041 1.178037 0.689056 0.011557 0.041965 0.007758 3 AUD...

www.access-programmers.co.uk

 

[mike60smart]

It may be producing the right results but you're absolutely doing it wrong.

Dlookups have no place in queries. You need to JOIN tblCurrencyCalculations to the data source(s) in the query already.

Hi Plog

I just knew there had to be a simpler method than what I was attempting.

Added the additional table to the query and all I needed then was the following:-

Rates: IIf([IncomingCurrency]=[Currency1],[FX Rate],0)

Many thanks

 

[Pat Hartman]

Why doesn't

Rate: DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [IncomingCurrency]

Work? I don't see the need for the IIf() In one case you are enclosing IncomingCurrency with single quotes and in the other you are not. The quotes are not dependent on the value of the field. They are dependent on the data type. So either they are always needed or they are never needed.

 

[mike60smart]

Hi Pat
This works:- Rate: DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [IncomingCurrency]& "'")

Thanks