Multi Criteria Dlookup with ComboBox

[mchr5900]

Hi All

Hoping you can help with a small issue

I am trying to Dlookup an account number from one table (Cleared) where the Product is a "Futures" and the account number matches a combobox

Stuck on the below
=DLookUp("[Citi IM]","Cleared","[Product] =" & "Futures" And [AcctNumber]=[AccountCombo])

Citi IM is a field name in table Cleared
Product is a field name in table Cleared
Futures is the criteria for Product field
Acct Number will be supplied in combobox

Currently getting #Name?

Thank you

 

[isladogs]

Assuming Product is a text field and AcctNumber really is a number field then:

=DLookUp("[Citi IM]","Cleared","Product ='" & Futures & "' And AcctNumber= " & Forms![B][I]YourFormName[/I][/B].AccountCombo)

Substitute the name of your form.
If this is being done from within the same form use Me. instead

=DLookUp("[Citi IM]","Cleared","Product ='" & Futures & "' And AcctNumber= " & Me.AccountCombo)

 

[mchr5900]

Hey isladogs

I tried both ways but seem to have not had any luck. Apologies if I am missing something

Going to try and give more details to see if that will help

Table name - Cleared (this is a linked excel sheet)
AcctNumber - Number format field in Table Cleared
Product - Short Text format field in Table Cleared
Citi_IM - Short Text format field in Table Cleared

Items include to show Formula in Form, and properties of textbox in which the formula is entered

Thank you again for your help

 

[Gasman]

Dlookup Has [City IM] and you say field is [City_IM] ?

 

[mchr5900]

That is correct. Citi_IM is a field name in the table. I am looking to extract the account number from that field when product type is Futures and Account Number matches a combobox on Form 1

 

[isladogs]

As Futures is a value and not a control then

=DLookUp("[Citi_IM]","Cleared","Product ='Futures' And AcctNumber= " & Me.AccountCombo)

Do NOT put [] except where shown

Your last reply makes me wonder whether this is what you really want.
It will return the value of Citi_IM that matches those two criteria

 

[mchr5900]

Agreed. I am trying to get the account number at a certain bank when product is futures and account number matches the combo box

I copy and pasted directly into the formula bar but still with no luck. Still getting #Name?

Does it make any difference that it is a linked excel sheet?

Filter Lookup is Database Default

If you are able to provide what the logic should be I can play around with it myself as opposed to wasting your time

The table looks like the below
AcctNumber Product Citi IM
11xxxxxx Futures 12345

So when AccountCombo matches AcctNumber and the product is Futures pull the Citi IM number on the cleared table

 

[isladogs]

This really doesn't make sense...

Agreed. I am trying to get the account number at a certain bank when product is futures and account number matches the combo box

Then surely the account number IS the value in the combo box
What is this field Citi_IM?

I copy and pasted directly into the formula bar but still with no luck. Still getting #Name?

Does it make any difference that it is a linked excel sheet?

The DLookup code is used in Access
A different function called VLookup is used in Excel and would use cell values to obtain a result

[/QUOTE]
If you are able to provide what the logic should be I can play around with it myself as opposed to wasting your time

The table looks like the below
AcctNumber Product Citi IM
11xxxxxx Futures 12345

So when AccountCombo matches AcctNumber and the product is Futures pull the Citi IM number on the cleared table[/QUOTE]

The final sentence is what the DLookup should achieve in Access except I think you mean when the AcctNumber matches the value in the AccountCombo.
I'm totally confused about what you are trying to do and it seems to me we are going around in circles

 

[mchr5900]

The below SQL statement pulled in the correct info but the AcctNumber and Products field were hidden

SELECT Cleared.Citi_IM
FROM Cleared
WHERE (((Cleared.AcctNumber)=[Forms]![Form1]![AccountCombo]) AND ((Cleared.Product)="Futures"));

Is it possible to convert this into the Dlookup?

 

[isladogs]

Already done in post #6.
That will also only display that one field Citi_M

 

[mchr5900]

=DLookUp("[Citi_IM]","Cleared","Product ='Futures' And AcctNumber= " & [Forms]![Form1]![AccountCombo])

This is currently working

But I don't know enough to say Why

The textbox in which the account number is populated is on the same form as the combobox

Sorry for the hassle. Appreciate the help.

Thank you again!

 

[isladogs]

It will still work using Forms! syntax but it should also work if the code is run from that form using Me. instead

Anyway pleased you've got a solution