Insert expression in a dlookup

[jibb]

I am using the following expression to look up prices with 2 criteria:

=DLookUp("[BON001]","Sheet1","Reference_Number]=[Combo100]and[Price Breaks] = 'Smalls' ")

Is there any way I could put the cell reference of a combo box where it says "[BON001]" so that it returns the prices for the specified account number?

Thanks in advance.

 

[jdraw]

You have a table "sheet1" with a field "BON001"

where does reference_number fit?

In plain English what is the condition you'd like to apply?

 

[jibb]

Sheet 1 has a list of product codes on the left and the fields are all named by the customer account number. when i open the form i want to put in the customer account number and product code which will pull the price for that customer and product. the reference number refers to the product code which is specified by a combo box.

trust this makes sense...

 

[jdraw]

the reference number refers to the product code which is specified by a combo box.

=DLookUp("[BON001]","Sheet1","Reference_Number]=" & me.[Combo100].value & " and [Price Breaks] = 'Smalls' ")

Try this, which assumes reference_number is a Number, not a string.

You didn't mention Price Breaks, but you should avoid names for fields and objects with special characters and/or spaces - these will come back to bite you.

 

[jibb]

The reference number is returning fine as it it is - that is linked to the "combo100". i was wanting to link the first part ("[BON001]") to a combo box ([Customer Details]![Combo104]) which contains the customer account numbers so that when i put the customer name in, it will automatically look up that customers prices.

Thanks for your help.

 

[Taruz]

Hi..:

try it..:

=DLookUp("BON001","Sheet1","Reference_Number=" & [Combo100] & " And [Price Breaks] = 'Smalls' ")

 

[jibb]

I'll try explain this a bit more clearly!

I have got a table with the following fields: Reference Number(Product Code), Price Break(Smalls, 1plt, 5plt, 10plt, 26plt etc), then all the customer account numbers (eg BON001, GNE001, SCR001 etc). I have then made a form based on this table so that when i open the form I type the customer account number into the first combo box (which pulls up the customer details) then I enter the product code into another combo box (which pulls up the product details) then it shows that customers prices for the product.
The following expression works fine:
=DLookUp("[BON001]","Sheet1","Reference_Number]=[Combo100]and[Price Breaks] = 'Smalls' ")

BUT I want the field name "[BON001]" to be controlled by the first combo box in the form. All the criteria in the expression after that are fine.

Many Thanks

 

[jibb]

Sorted!

=DLookUp("=[" & [Customer Ref].[Value] & "]","Sheet1","[Reference_Number] = [Combo100] and [Price Breaks] = 'SMALLS' ")