Dlookup with Multiple criteria

mba_110

Registered User.
Local time
Today, 15:38
Joined
Jan 20, 2015
Messages
280
Hi

I have following dlookup for multiple criteria but it is not producing the desired result, i am pulling the exchange rate for particular currency on particular date from tblExchangeRates.

Code:
=DLookUp("[Rate]","tblExchangeRates","[Currency] = '" & ![cboForeignCurrency] & " And [ExhDate] = #" & [txtDate] & "#" & "'")

the PK of tblExchangeRates is [ExchangeID]

result text box is blank and no errors.
 
Last edited:
The single quotes can't be used like that. They should be used to enclose individual text fields
In fact, assuming Currency is a number field, the single quotes need to be removed as does the !

Code:
=DLookUp("[Rate]","tblExchangeRates","[Currency] = " & [cboForeignCurrency] & " And [ExhDate] = #" & [txtDate] & "#")

If this I being done from outside the form, then need to add form references.
 
If this is on a form what /where is ![cboForeignCurrency]

If it's on the form you are on simply use Me.cboForeignCurrency . Intellisense will help you identify the form references. This is something you should be used to by now.

If you used a variable to store the data you could easily debug this yourself.

Code:
Dim sForCurrency as String

sForCurrency = Me.cboForeignCurrency 
Debug.Print sForCurrency

etc....
It's more typing but will help you in the learning phase.

Secondly is this a string or a number? At the moment it looks like you are handling it as a number, but is it a string. Have a read of the links in my signature.
 
Currency is a field name and its string and not field type (number).

Got it.....
Code:
=DLookUp("[Rate]","tblExchangeRates","[Currency] = '" & [cboForeignCurrency] & "' And [ExhDate] = #" & [txtDate] & "#")
 
Why use text for currency? Very odd thing to do. Anyway, quotes restored but in the correct places

Code:
=DLookUp("[Rate]","tblExchangeRates","[Currency] = '" & [cboForeignCurrency] & "' And [ExhDate] = #" & [txtDate] & "#")

EDIT
You got there quicker.
Using text means you can't sort sensibly by currency as it will be sorted 'alphabetically' e.g. 132156.00 will be treated as less than 255.00. Also doing sums will be harder. Use number or currency datatype instead.
 

Users who are viewing this thread

Back
Top Bottom