I am having a problem with DLookUp. What I want to do is to look at a particular field in a table and return the corresponding value in the criteria section. My DLookUp works perfectly and looks like this;
=DLookUp([80],"Products",[Pronto]=[Product])
So the expression looks at the column labelled 80 in my Products table and returns the value in the Pronto column = to the value specified in the Product field. However, what I want to do is to substitute [80] with a field in a query called [Sell Code] so that it looks at the value in this field to define which column to select in the table and return the value based on the criteria, i.e.
=DLookUp([Sell Code],"Products",[Pronto]=[Product])
When I do this, it returns the value that is in the Sell Code field only. Anyone with any suggestions?
See attachment for explanation.