DLookup Add extra Field

[burrina]

Here is my code for DLookup, I want it to also lookup the Supplier form the same table. Not sure how to add to it!



DLookUp("UnitPrice","Products","ProductID = " & [ProductID])

[jzwp11]

To clarify, do you want the dlookup() to return both the unitprice and the supplier of the product identified by the productID or do you want to find the unit price based on both the productID and the supplier?

[burrina]

I need both. Yes, both based on ProductID..

Thanks,

[jzwp11]

If you need to pull the unit price based on both the productID and the supplier, the dlookup() would look like the following. This assumes that the supplierID field is a field in the Products table.

DLookUp("UnitPrice","Products","ProductID = " & [ProductID] & " AND SupplierID=" & [SupplierID])

However, if you want this:

DLookUp("UnitPrice_Supplier","Products","ProductID = " & [ProductID])

That cannot be done in the dlookup() function as you currently have it set up.

You will have to create a query that concatenates the supplier with the unit price and then reference that field and that query in the dlookup()

For example, the query might look like this:


query name: qryUnitPriceWithSupplier
SELECT unit_price & "_" & supplierID as UnitPriceAndSupplier, productID
FROM Products

The Dlookup() would look like this:

Dlookup("UnitPriceAndSupplier","qryUnitPriceWithSu pplier", "ProductID=" & [ProductID])

When you do this, the UnitPriceAndSupplier field will no longer be numeric but rather text, so you will not be able to conduct any arithmetic operations with that field.

[John_G]

jzwp11 & burrina -

Actually, and perhaps surprisingly, you CAN include an expression in DLookup!

A DLookup like this works fine : dlookup("duty_ID * Seq_num","job_statement","Job_Statement_ID = -178426"), and does return a numeric. You can include constants as well.

Burrina, your Dlookup could look something like this:

DLookUp("UnitPrice & ' ' & Products","ProductID = " & [ProductID])

But as jzwp11 pointed out, the result would be a string, and you would have to use more VBA to separate the parts.

John

[jzwp11]

Thanks John--learn something new about Access everyday.