Hi Richard,

I appreciate your feedback with the Dlookup function. I now have another question where I'm trying to Dlookup the price from a table


Table 1 should return $8.49 and not $7.99

Example Table 1

Item Group PSRP ComparePSRP Code
27971 A $8.49 $7.99 BR
27971 B $8.49 $7.99 BR



The problem I'm having is, that it's returning the first price of $7.99 and not $8.49 which I'm using as the Dlookup criteria.

I need your guidance on this once as I've been struggling to get my head around this for two days now.


VBA code example below:

rs.MoveFirst

       Do Until rs.EOF

               rs!ComparePSRP = Nz(DLookup("PSRP1", "Table2", "ItemNumber=" & rs!Item & " AND Code='" & rs!Code & "'"))
rs.MoveNext
      Loop

Week ItemNumber Group PSRP1 Code
43 27971 A $7.99 AS
26 27971 B $7.99 BR
40 27971 A $7.99 BR
26 27971 B $7.99 BR
28 27971 A $8.49 BR
8 27971 B $8.99 AS
28 27971 A $8.49 BR
31 27971 B $8.49 BR


Your expert advice is much appreciated

Regards

Hamish

---

Reply from Richard Rost:

DLOOKUP by itself is going to find the FIRST record in the table that meets the criteria. If you want the MOST RECENT data, you're going to need a second statement using DMAX to determine the highest ID that meets those criteria, then DLOOKUP the value using that ID.