Multiple criterias in Dlookup function

[maxchang9]

First of all, i wrote my access in Chinese, so instead of screenshots ill explain the problem the best i can
---------------------------------------------------------------------------
Table A

Number	Ingredient
1	A
2	B
3	C
4	D

TableB

OrderNumber	Import	OrderPrice
1	A	100
1	B	50
2	A	500
2	C	5
2	D	2000
3	B	40
3	D	1000

(Table A ! [Ingredient]) 1 ~ ∞ (Table B ! [Import] )

Query only include TableA


---------------------------------------------------------------------------------------------------------------------------------
I want to make a Query indicate the latest OrderPrice (which has the largest OrderNumber) of each Ingredient

What i expect:

Number	Ingredient	LatestPrice
1	A	500
2	B	40
3	C	5
4	D	1000

So far i got it right if each criteria run "independently"

Latestprice: DLookUp("OrderPrice","TableB","OrderNumber=" & DMax("OrderNumber","TableB") & "" ) 'find OrderPrice with largest OrderNumber

Number	Ingredient	LatestPrice
1	A	1000
2	B	1000
3	C	1000
4	D	1000

Latestprice: DLookUp("OrderPrice","TableB", "[Import]='" & [Ingredient] & "'") 'find OrderPrice with corresponding Ingredient

Number	Ingredient	LatestPrice
1	A	100
2	B	50
3	C	5
4	D	2000

However, when i combine these as:
Latestprice: DLookUp("OrderPrice","TableB","OrderNumber=" & DMax("OrderNumber","TableB") & "" And "[Import]='" & [Ingredient] & "'")

The 1st criteria (Dmax) seems to overwrite the 2nd criteria

Number	Ingredient	LatestPrice
1	A	1000
2	B	1000
3	C	1000
4	D	1000

So, where did i did wrong?

Please help me out, and thank you .
---------------------------------------------------------------------------------------------------------------------------
heres the screenshot of my query

[ArviLaanemets]

Latestprice: DLookUp("OrderPrice","TableB","OrderNumber=" & DMax("OrderNumber","TableB") & "" And "[Import]='" & [Ingredient] & "'")

Create a saved query

qLatestOrder = SELECT Ingredient, MAX(OrderNumber) As LatestOrder FROM TableB GROUP BY Ingredient;

Your query will be

SELECT lo.Ingredient, lo.LatestOrder, tb.OrderPrice AS [LatestPrice] FROM qLatestOrder lo LEFT JOIN TableB tb ON lo.LatestOrder = tb.OrderNumber AND lo.Ingredient = tb.Ingredient

[maxchang9]

You mean make query directly from SQL? I tried and it demands me to enter Ingredient parameter value 3 times, in the end i got a table with 3 columns: ingredient + latestOrder + LatestPrice (which doesnt belong to the ingredient).

[ArviLaanemets]

???
Both queries read data from table TableB, and return latest orders and latest prices for all ingredients.

Now I see in your post #1, that there is it for some reason instead Ingredient used Import as column name - missed it yesterday! Replace "Ingredient" with "Import" in both query definitions!

[maxchang9]

Big thanks, i finally solve it myself with similar method (2 queries) you suggest. I did it in design view though, since i am not familiar with SQL code. Just curious how does " lo. " and " tb. " works?

[dashiellx]

I'm not sure DLookup is the way to go here. I think you might need to create a custom function to do what you want.

Something along the lines of:

Public Function GetLastPrice(strIngredient as String) as Currency
Dim strSQL as String
Dim rstLastPrice As DAO.Recordset
Dim currTemp as Currency

strSQL = ""
strSQL = strSQL & "SELECT Max(TableB.OrderNumber), TableB.Import, Last(TableB.OrderPrice)
strSQL = strSQL & " FROM TableB
strSQL = strSQL & " WHERE (((TableB.Import)='" & strIngredient & "'))"
strSQL = strSQL & " GROUP BY TableB.Import;
set rstLastPrice = currentdb.OpenRecordset(strSQL, dbreadonly)
If not rstLastPrice.eof and not rstLastPrice.BOF then 
    currTemp = rstLastPrice(0)
Else
   currTemp = 0
End if
GetLastPrice=currTemp 
End Function

*Results and capitalization will vary.

[maxchang9]

Thank you very much I finally solved it myself with the method u suggest (2 queries). I have to do it in design view since i am not familiar with SQL code yet. Just curious how does " lo. " and " tb. " works?

[dashiellx]

Just curious how does " lo. " and " tb. " works?

when writing SQL, rather than having to type the full table name all the time, you can use an Alias in the From statement of you query and then use that Alias in the Select, Where, Order and Group By clauses.

[maxchang9]

Indeed, i give up dlookup after hours of attempt, the main problem is because 2nd criteria will eliminate the results in 1st criteria. I also messed up the syntax too, Since OrderNumber is numerical + "" should surround AND, the correct syntax is:
DLookUp("OrderPrice","TableB","OrderNumber= & DMax("OrderNumber","TableB") And [Import]='" & [Ingredient] & "'")

Anyway thanks for the reply, ill study your code during weekend

[ssanfu]

DLookUp("OrderPrice","TableB","OrderNumber= & DMax("OrderNumber","TableB") And [Import]='" & [Ingredient] & "'")

You are missing a closing double quote after "OrderNumber=" and an amphersand and double quote at the end of the DLookup function

DLookUp("OrderPrice","TableB","OrderNumber= " & DMax("OrderNumber","TableB") & " And [Import]='" & [Ingredient] & "'"