DLookup to Get Latest Pricing

[istjustoldcardboard]

In a field on a form I am trying to retrieve the latest pricing information based on the newest date.

tblChecklistPricing
pkCardID curCardBVPrice dtmCardPricingC
585 1.00 4/14/2019
586 1.25 4/14/2019
588 1.25 4/14/2019
585 1.50 4/16/2019

So in the example above, I would like to display "1.50" in the curCardBVPrice field for record #585, since it is the latest date. I tried using the example below but it returns nothing in the field.

=DLookUp("[curCardBVPrice]","tblChecklistPricing","[pkCardID] = " & DMax("[dtmCardPricingC]","tblChecklistPricing","[pkCardID]"))

Thanks!

 

[theDBguy]

Hi. Welcome to the forum! I think you were almost there. Try:

=DLookUp("[curCardBVPrice]","tblChecklistPricing","[pkCardID] = " &  [pkCardID] & " AND dtmCardPricingC=#" & Format(DMax("[dtmCardPricingC]","tblChecklistPricing","[pkCardID]=" & [pkCardID]),"yyyy-mm-dd") & "#")

Hope it helps...

 

[Gasman]

You are not using Dmax correctly? You are trying to compare the ID to a date?

https://www.techonthenet.com/access/functions/domain/dmax.php

From your example try

=DLookUp("[curCardBVPrice]","tblChecklistPricing","[pkCardID] = " & DMax("[pkCardID]","tblChecklistPricing"))

 

[June7]

Are you selecting product with a combobox? Include the price field as a combobox column and reference the combobox column for the price. No DLookup needed. Have combobox RowSource list only the current valid product/price records.

But if you must, I think theDBGuy has the best structure.

 

[istjustoldcardboard]

Hi. Welcome to the forum! I think you were almost there. Try:

=DLookUp("[curCardBVPrice]","tblChecklistPricing","[pkCardID] = " &  [pkCardID] & " AND dtmCardPricingC=#" & Format(DMax("[dtmCardPricingC]","tblChecklistPricing","[pkCardID]=" & [pkCardID]),"yyyy-mm-dd") & "#")

Hope it helps...

Thank you! This did the trick. I had to modify a bit because of the foreign key but this is what i am using:

=DLookUp("[curCardBVPrice]","tblChecklistPricing","[fkCardID] = " & [Forms]![frmChecklistCard]![pkCardID] & " AND dtmCardPricingC=#" & Format(DMax("[dtmCardPricingC]","tblChecklistPricing","[pkCardID]=" & [pkCardID]),"yyyy-mm-dd") & "#")

 

[theDBguy]

Thank you! This did the trick. I had to modify a bit because of the foreign key but this is what i am using:

=DLookUp("[curCardBVPrice]","tblChecklistPricing","[fkCardID] = " & [Forms]![frmChecklistCard]![pkCardID] & " AND dtmCardPricingC=#" & Format(DMax("[dtmCardPricingC]","tblChecklistPricing","[pkCardID]=" & [pkCardID]),"yyyy-mm-dd") & "#")

Hi. Congratulations! Glad to hear you got it sorted out. We're all happy to assist. Good luck with your project.