DLookup in query product no results

[FranS37]

I'm using this DLookup in a query. It should be producing results, but is not. Can anyone see why? Thanks

Expr1: DLookUp("Manufacturer","Products","[InventoryTransactions].[ProductCode]"="[Products].[ProductCode]&quot

Fran

 

[hneal98]

Hi Fran,

It should be like this:

Expr1: DLookUp("Manufacturer","Products","[InventoryTransactions].[ProductCode]=[Products].[ProductCode]&quot

Your criteria section should completely in quotes, so you your example, instead of putting quotes around each field, it should be around the whole statement. Let me know if that does not make sense.

 

[FranS37]

Thanks for your help. Now I'm getting an error saying "Access can't find the name InventoryTransactions.ProductCode that you entered in the expression.

InventoryTransactions is the table that the query is based on.

 

[hneal98]

Can you paste the SQL here? I see what you are trying to do, but I need to see the query itself. Click on the SQL button on the button bar and copy the SQL statement and paste it here.

 

[FranS37]

Yes, thanks.

SELECT InventoryTransactions.ProductCode, DLookUp("Manufacturer","Products1","[inventoryTransactions].[ProductCode]=[Products1].[ProductCode]&quot AS Expr1, InventoryTransactions.TransactionID, InventoryTransactions.TransactionDate, InventoryTransactions.PurchaseOrderNo, InventoryTransactions.TransactionDescription, InventoryTransactions.UnitPrice, InventoryTransactions.UnitsOrdered, InventoryTransactions.UnitsReceived, InventoryTransactions.UnitsSold, InventoryTransactions.UnitsShrinkage
FROM InventoryTransactions;

 

[hneal98]

It looks like you are trying to use the dlookup to pull data from a diferent table other than the one you have on your query. That would not work. Dlookup only works on the current dataset. Here is how you should rewrite your query:

SELECT
InventoryTransactions.ProductCode,
Manufacturer,
InventoryTransactions.TransactionID,
InventoryTransactions.TransactionDate,
InventoryTransactions.PurchaseOrderNo,
InventoryTransactions.TransactionDescription,
InventoryTransactions.UnitPrice,
InventoryTransactions.UnitsOrdered,
InventoryTransactions.UnitsReceived,
InventoryTransactions.UnitsSold,
InventoryTransactions.UnitsShrinkage
FROM
InventoryTransactions
inner join Products1 on [inventoryTransactions].[ProductCode]=[Products1].[ProductCode];


Hope this helps.

 

[FranS37]

What would be the reason for using DLookup on the current dataset if you could just refer to the field?

Well, what I'm trying to do is make the recordset updateable and if I do a join like that the recordset is not updateable.

Any ideas? Thanks for your help.

Fran

 

[hneal98]

You are correct. It would not be updateable. The DLookup is only for referencing data, but you can only do it with the current dataset. If you try to do it with another table, Access will complain that it can't find the table because the table is not in the current query as a linked table.

Unfortunately, what you would have to do is save the data you want put into your tables and after all changes have been made on the form, have a button that saves the data to each table that needs to be updated. There are just times where you can't easily update.

If you have further questions, please ask.

 

[FranS37]

Ok, thanks for your help.

Fran