Dlookup Function Help ???

[pkumar74]

Hi .........

I'm not good at Access but always try to make things work.....'cause i have habbit of correcting my self.

I have a database , in which I have a form for purchase orders with the following fields.
ProductId
ProductDescript ion
UnitPrice

What I'm trying to do is to dlookup is the Price & the ProductDescript ion, when ProductId is typed.

DLookUp("UnitPr ice","Products" ,"ProductID = " & [ProductID])

I am successful in getting UnitPrice as soon as I type the ProductID but not the ProductName. Is is possible to include it in the same expression.

Can u please help me............

Thanks
Prashant

[wassimdaccache]

Yes I did that once of course getting help from thescripts :)


just change the criteria that you want

DLookUp("UnitPr ice","Products" ,"productnam e= " & [productname])

DLookup(expr, domain, [criteria])

BE SURE TO HAVE THE SAME FIELD NAME ON THE TABLE THAT YOU ARE LOOKING FOR

[missinglinq]

Sorry, but the OP wants to retrieve both the UnitPrice and the ProductName!

DLookUp("UnitPr ice","Products" ,"productnam e= " & [productname])

will simply retrieve the UntiPrice again, using ProductName as the criteria instead of using ProductID.

The only way I know to retrieve multiple fields with a single DLookup would be to base the lookup on a query and have a calculated field in the query that concatenates the two desired fields, then use that field as the field to be retrieved.

In the query named have a field PriceAndName defined as

PriceAndName: [UnitPrice] & " " & [ProductName]

Then use

[CODE=vb]Private Sub Command3_Click( )
results = DLookup("PriceA ndName", "YourProductsQu ery", "ProductID = " & [ProductID])
End Sub
[/CODE]
Depending on your needs, you'd then have to parse the results if you need the ProductName and UnitPrice as separate entities.

You could, of course, use a SQL statement to pull the info, or use a combobox to select the ProductID and then return the name and price, but you asked how to do this with a single DLookup statement.

Welcome to TheScripts!

Linq ;0)>

[pkumar74]

Yes I did that once of course getting help from thescripts :)


just change the criteria that you want

DLookUp("UnitPr ice","Products" ,"productnam e= " & [productname])

DLookup(expr, domain, [criteria])

BE SURE TO HAVE THE SAME FIELD NAME ON THE TABLE THAT YOU ARE LOOKING FOR

Thank you for your early reply:

but if I change the ProductID to ProductName, then I get nothing in the field.

My primary search is based on productid & not on product name. for ex.

ProductId ProductName UnitPrice

1234 ABC $ 20
2345 DEF $ 10
6789 PQR $ 15

When i create a purchase order, in the productid field when I key in 1234, it should automatically show me ABC & $ 20 in their respective fields.

I will appreciate if you could write a code for this purpose.

Thanks for your help........... ........