Dlookup syntax for text criteria

[jd_boss_hogg]

Pulling my hair out with this - have spent over an hour retyping it, trying to get it to work. Can anyone tell me where i'm going wrong?

I'm in a report called "Mask_order_book". There is a field on this report called "CUST_REF" (a text field). I want to look up this value in the "Product" field (text) in a table called "Pricelist", and then return the 'Price' value from that table.

I'm using...

=IIf([NET]=0,DLookUp("Price","Pricelist","[Product] = '" & [Reports]![Mask_order_book]![CUST_REF] & "'"),[NET])

It's just the criteria that doesn't work. I get #error returned on the report when ran.

Thanks in advance.

 

[pl456]

Take a step back and work through eveytyhing again. Check the link and knock each one off the list.


http://office.microsoft.com/en-us/access/HA011814481033.aspx

 

[KenHigg]

See if this works:

=IIf([Reports]![Mask_order_book]![NET]=0,DLookUp("[Price]","[Pricelist]","[Product] = '" & [Reports]![Mask_order_book]![CUST_REF] & "'"),[Reports]![Mask_order_book]![NET])

Actually, I would do all of this in the reports underlying query.

 

[jd_boss_hogg]

Hi guys...

ken - that didn't work, so i stuck it in the query as you suggested like this....
Expr1: IIf([NET]=0,DLookUp("[Price]","[Pricelist]","[Product] = '" & [CUST_REF] & "'"),[NET])

and i'm getting some very strange things happening. So, investigated a bit futher and found the culprit. The PRODUCT field in the table Pricelist is actually a lookup itself from another table called Productlist ! So, i'm trying to do a lookup on a lookup. I didn;t set this part of the dbase up, so only just realised.

Now i'm really stuck !

 

[KenHigg]

I have the boss peeking around and need to get back to work. Maybe pl456 can help

 

[pl456]

A Dlookup on a Dlookup will work.
Can you put the first of the Dlookups (the one you didn't know about) into the reports query as Expr1 and then run your second Dlookup in a control on the report using the criteria
= '" & [Reports]![Mask_order_book]![Expr1] & "'"

 

[jd_boss_hogg]

Hi Ken - PL456,

Spent a whole day yesterday, and still can't fathom this out. I'd really appreciate any help you have, cause i'm beat !

I have a table (ProductList) with fields: ID, Code, Description
I have a table called (PriceList) with fields: PriceID, Product, price


In the table PriceList, the field 'product' is actually a lookup , using this command... SELECT [ProductList].[ID], [ProductList].

, [ProductList].[Description] FROM ProductList ORDER BY [ID]; , so 'code' and 'product' have a relationship.

I want to query the "price" field using a Lookup, using 'product' as my criteria. Problem is, i can't get anything returned because 'product' is actually a lookup so i suppose contains no 'real' values.

In my query, i'm using...
Expr1: DLookUp("[Price]","[Pricelist]","[Product] = '" & [CUST_REF] & "'")

Heres the really confuding bit. This DOES return a [price] value, but only when "productlist.id = cust_ref". So, i think what's happening is that the query condition is looking for [product], which is a lookup from the 'productlist' table and is checking the first column in this table rather than the required one?

 

[Rich]

You don't need DLookUp in the Reports underlying query, just add the lookup table to the query make sure the joins are correct and then select the relevant field for the Report

 

[pl456]

CUST_REF is a field on your report, what is the control source of that control?

 

[jd_boss_hogg]

Rich - OK, i've got rid of the lookups, and just added the table to the query and then added the criteria. Things look much simpler now. However, it's still acting the same way. here is my souce for the query....

SSELECT IIf([NET]=0,[PriceList]![Price],[NET]) AS Expr1, *
FROM [jb-2001], PriceList
WHERE (([cust_ref]=[product]))

This returns all the items where cust-ref = ProductList.Code ??????? but that isn't the criteria. Obviously something to do with pricelist.product being a lookup of table ProducList.

 

[jd_boss_hogg]

PL456 - i've followed rich's advice and i've decided to go back to basisc. I'm trying to get the query working first before i do anything with the reports etc. Does the previous response from me make sense?

BTW, i appreciate all the great help i'm receiving. I'm very much a newbie and i'm learning some great stuff as i stumble from one thing to another !

 

[pl456]

Sound like the value being stored from the lookup (bound column) in table design is putting the pricelistcode in when the column is called product.

That is probably by design so you may not get away with changing it. The knock on could be bad for other parts of the database.

 

[jd_boss_hogg]

I tried changing the bound column, but it didn;t matter which column i picked, the query always returned column 1 of the linked table. Looks like i'm gonna open up a whole new can of works and try to add the price into the products table - never understood whyy it wasn;t in their anyway !

 

[pl456]

Sorry must have missed this bit, what is jb-2001? I though you where looking at ProductList & PriceList tables.

Rich made the commment about adding the field to query and checking your joins.
How are these two joined?

Build your query based on these two tables (providing they are the correct tables of data, I don't know if they are but they sound like they might be right). Keep it simple so you get the products listing with the price. Then start working on criteria to restrict the results.