Help please with dlookup (Error 3075 : Syntax error (missing operator) in query expression )

Question

I am trying to use a dlookup to lookup up the stock code for an item from a query based on the selection a user makes in a combo box on the form......i.e. they select the description of the product and the dlookup returns the stock code..

The field name of the value I am trying to retrieve is StockCode.

The query name is qry_ProdStockCodeList.

The name of the form the form is  frmProduction.

The name of the combo box is ProdDesc.

My dlookup function looks like:

strTest = DLookup("[StockCode]", "[qry_ProdStockCodeList]", "[Desc]=" & [Forms]![frmProduction]![ProdDesc])

Each time the I make a selection of a product description in the combo box I get the following error message:

3075 : Syntax error (missing operator) in query expression '[Desc]=CORTLAND TRAY 72'. 

If I have the equal sign what operator is missing?   Can anyone help me out please.

Thanks.

Answer 1

Hi,

You must wrap text values inside quotes:

strTest = DLookup("[StockCode]", "[qry_ProdStockCodeList]", "[Desc]=""" & [Forms]![frmProduction]![ProdDesc] & """")

Let me know if you have more questions.

Answer 2

Hi,

You must wrap text values inside quotes:

strTest = DLookup("[StockCode]", "[qry_ProdStockCodeList]", "[Desc]=""" & [Forms]![frmProduction]![ProdDesc] & """")

Let me know if you have more questions.

Awesome...….thanks for your help.

Answer 3

In my opinion you are doing this backward. There should be no need for a Dlookup.

The Rowsource for your ProdDesc combo should be :

SELECT StockCode, Desc FROM ProdStockCodeList ORDER BY DESC;

Now if you want to display both the StockCode and Desc then add an unbound text box with a ControlSource of

=[ProdDesc]

The combobox should be bound to a StockCode foreign Key in your table, adding the Desc is redundant.