Lookup (1 Viewer)

[mike60smart]

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	1.368431​	1.582528​	1​	1.86386​	1.090207​	0.01829​	0.066397​	0.012275​
4​	GBP	0.734036​	0.848879​	0.536521​	1​	0.584919​	0.009811​	0.035623​	0.006586​
5​	CAD	1.255114​	1.451482​	0.917257​	1.709638​	1​	0.016775​	0.060903​	0.011259​

On My Form I have a Control named "Currency"

In another Unbound Control I need to lookup a value from the tblCurrencyCalculations.

So, If ([Currency]="USD",Dlookup to retrieve the value in Row 1 Column GBP (1.362368)

I just can't get the syntax to work for me.

I have tried :- =IIf([Currency]="USD",DLookUp("USD","tblCurrencyCalculations","Currency = '" & [Currency] & "'"))

This returns a figure 1

Any help appreciated

 

[oleronesoftwares]

Use the currency id in the dlookup as parameter

=DLookup("[GBP]", "tblCurrencyCalculations", "[CurrencyID] =" _ & Forms![currentform]!CurrencyID)

 

[bastanu]

Actually I think you need to make the column you search dynamic:
=DLookup(""" & Forms![frmYourForm]!UnboundControl] & """, "tblCurrencyCalculations", "[Currency] ='" & Forms![frmYourForm]![Currency])
Cheers,

 

[arnelgp]

i think all your conversion is in GBP?

=Dlookup("GBP", "tblCurrencyCalculations", "[Currency]='" & [Currency] & "'")

 

[CJ_London]

I have tried :- =IIf([Currency]="USD",DLookUp("USD","tblCurrencyCalculations","Currency = '" & [Currency] & "'"))

This returns a figure 1

It will do - according to your table, the value in the USD column when currency=USD is 1

ah- arnel got there before me

 

[Minty]

Although that table design is great for a human to look up, it's poor for a database.
You should have

CurrID, BaseCurr, ComparisonCurr, Rate
tblCurr tblCurr

CurrencyID	BaseCurrency	ComparisonCurr	Rate
2​	USD	USD	1​
3​	EURO	USD	0.864712​
4​	AUD	USD	​
5​	GBP	USD	​
6​	CAD	USD	​
7​	INR	USD	​
8​	MXN	USD	​
9​	JPY	USD	​
10​	USD	EURO	​
11​	EURO	EURO	​
12​	AUD	EURO	​
13​	GBP	EURO	​
14​	CAD	EURO	​
15​	INR	EURO	​
16​	MXN	EURO	​
17​	JPY	EURO	​
18​	USD	AUD	​
19​	EURO	AUD	​
20​	AUD	AUD	​

You get the idea.

 

[mike60smart]

i think all your conversion is in GBP?

=Dlookup("GBP", "tblCurrencyCalculations", "[Currency]='" & [Currency] & "'")

Hi Arnelgp

That is spot on.

Minty
Yes I know it is not ideal but that is what the current users are working with.
I will try to convince them to change.

Many thanks everyone for the help

 

[Pat Hartman]

Don't you also need a date in the table?

I've attached a sample that will allow you to use the properly normalized schema shown by Minty and still give your users a form that looks like a spreadsheet.