DLookup (1 Viewer)

mike60smart

Registered User.
Local time
Today, 01:06
Joined
Aug 6, 2017
Messages
1,177
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.
 

Attachments

  • Currency.JPG
    Currency.JPG
    34.3 KB · Views: 190

Minty

AWF VIP
Local time
Today, 01:06
Joined
Jul 26, 2013
Messages
9,286
You have a space in your field name, try putting square brackets around it;

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

mike60smart

Registered User.
Local time
Today, 01:06
Joined
Aug 6, 2017
Messages
1,177
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] & "'",""))))
 

Attachments

  • Error.JPG
    Error.JPG
    17.7 KB · Views: 178

Minty

AWF VIP
Local time
Today, 01:06
Joined
Jul 26, 2013
Messages
9,286
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

Super Moderator
Staff member
Local time
Today, 01:06
Joined
Feb 19, 2013
Messages
14,367
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] & "'"))
 

plog

Banishment Pending
Local time
Yesterday, 19:06
Joined
May 11, 2011
Messages
10,916
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

AWF VIP
Local time
Yesterday, 17:06
Joined
Apr 13, 2010
Messages
1,173
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.
 

mike60smart

Registered User.
Local time
Today, 01:06
Joined
Aug 6, 2017
Messages
1,177
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

Super Moderator
Staff member
Local time
Yesterday, 20:06
Joined
Feb 19, 2002
Messages
36,296
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

Registered User.
Local time
Today, 01:06
Joined
Aug 6, 2017
Messages
1,177
Hi Pat
This works:- Rate: DLookUp("[FX Rate]","tblCurrencyCalculations","[Currency1]='" & [IncomingCurrency]& "'")

Thanks
:)
 

Users who are viewing this thread

Top Bottom