DLookUp in Query

[missangie]

I have two tables - Products2 and FPSUM

I am creating a query to look up the Product Code from the FPSUM table and return a Product value (text field) from the Products2 table.

I have tried the following DLookUp and receive an error "Syntax error (missing operator) in query expression 'PI Code1=PI Code'. When I click ok, I receive an unknown error.
The fields that are related are PI Code in the FPSUM table and PI Code1 in the Products2 table.

DLookUp("Product","Products2","PI Code1='" & "PI Code" & "'")

Thanks,

Angela

 

[apr pillai]

Corrected Code is given below:

DLookUp("Product","Products2","[PI Code1]='" & [PI Code] & "'")

 

[missangie]

Thank you for the corrected dlookup; however, when I run the query I have no results at all now.

 

[apr pillai]

Are you sure that [PI Code1] and [PI Code] both have the same data type Text? Is there a space between Code & 1?

 

[missangie]

They are both text fields. PI Code in FPSUM is a combo box which retrieves it's value from a query based off the Products2 table. PI Code1 on the Products2 table does not have a space between Code and 1 and is a text field and is a text box, not a combo box.

 

[missangie]

I think it has something to do with PI Code in the FPSUM table as being a combo box. I designed it that way because I have five other fields on a form that autopopulate based on what is selected for PI Code. However, the information is not pulling into a query. If the PI Code is entered as straight text the query works fine but then I would either 1) not have the other five fields autopopulate or 2) would need to enter the PI Code twice, once to get it to show up in the query, and once to autopopulate. Yes, I know this is redundant; I just have no other idea on how to get this to work.