DLookup to Get Latest Pricing (1 Viewer)

istjustoldcardboard · Apr 16, 2019

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 · Apr 16, 2019

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

Code:

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

Hope it helps...

Gasman · Apr 16, 2019

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

Code:

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

June7 · Apr 16, 2019

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 · Apr 16, 2019

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

Code:

=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 · Apr 16, 2019

istjustoldcardboard said:
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.

DLookup to Get Latest Pricing (1 Viewer)

istjustoldcardboard

New member

theDBguy

I’m here to help

Gasman

Enthusiastic Amateur

June7

AWF VIP

istjustoldcardboard

New member

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread