Solved Get Latest Price Based On User Input To Display On A Form

[LGDGlen]

Hi All

i hope this one is a simple one

I have a form that the user enters some information into and then a button to look up latest price for the input they have given and populate that on the form as well.

My price table looks like this:

Price Table
Customer	Arrival Date	Fruit	Unit Count	Unit Weight (KG)	Selling Currency	Selling Price
Cust1	29/10/2021​	Raspberries	20​	0.15​	EUR	5​
Cust1	05/11/2021​	Raspberries	20​	0.15​	EUR	5.5​
Cust1	12/11/2021​	Raspberries	20​	0.15​	EUR	5.75​

The user inputs the following data

User Input
Customer	Arrival Date	Fruit	Unit Count	Unit Weight (KG)
Cust1	02/11/2021​	Raspberries	20​	0.15​

So in the code for the button matches customer, fruit, unit count and unit weight, but the issue i'm having is:

• Should it be FindFirst or FindLast?
• Should it be Me.ArrivalDate <= Table.ArrivalDate or Table.ArrivalDate <= Me.ArrivalDate

I'm getting a little bit frustrated with myself because i'm sure its very straight forward but i'm just struggling to get the right values from the price table

sellPricesRS.FindLast "[Customer] = '" & delsCustomer & "' AND " & CSql(delsArrivalDate) & " <= [Arrival Date] AND [Fruit] = '" & delProdProduct & "' AND [Unit Count] = " & Me.del_prod_count & " AND [Unit Weight (KG)] = " & Me.del_prod_weight

sellPricesRS.FindLast "[Customer] = '" & delsCustomer & "' AND " [Arrival Date] <= " & CSql(delsArrivalDate) & " AND [Fruit] = '" & delProdProduct & "' AND [Unit Count] = " & Me.del_prod_count & " AND [Unit Weight (KG)] = " & Me.del_prod_weight

sellPricesRS.FindFirst "[Customer] = '" & delsCustomer & "' AND " & CSql(delsArrivalDate) & " <= [Arrival Date] AND [Fruit] = '" & delProdProduct & "' AND [Unit Count] = " & Me.del_prod_count & " AND [Unit Weight (KG)] = " & Me.del_prod_weight

sellPricesRS.FindFirst "[Customer] = '" & delsCustomer & "' AND " [Arrival Date] <= " & CSql(delsArrivalDate) & " AND [Fruit] = '" & delProdProduct & "' AND [Unit Count] = " & Me.del_prod_count & " AND [Unit Weight (KG)] = " & Me.del_prod_weight

I'm not sure which one of those is the right way and i think i've tried a few different ways and it will work for 1 date input but not another, or if there are multiple records in the table for different dates there is different results, and i'm getting confused by the whole thing so thought i'd ask for someone's clarity who knows what it should be so i can go with that and test it in the different cases i have

thanks in advance

Glen

 

[theDBguy]

Hi Glen. Which record are you trying to grab from the sample price table above? Is it the one with the 29/10/2021 date, or something else?

 

[LGDGlen]

@theDBguy it would be the record dated the 5th as the arrival date is before that, it shouldn't pick up the 29/10/2021 and shouldn't pick up the 12/11/2021 (hahaha just had visions of the holy hand grenade instructions from Monty Pythons Holy Grail)

so for the arrival of 02/11/2021 the price should be EUR - 5.50

 

[theDBguy]

so for the arrival of 02/11/2021 the price should be EUR - 5.50

Just to make sure I'm clear, don't you meant the price should be 5 EUR?

 

[LGDGlen]

the arrival prices are any date from 30/10/2021 to 05/11/2021 should show a price of 5.50, anything up to 29/10/2021 should show 5.00 and anything from 06/11/2021 to 12/11/2021 should show price of 5.75, if an arrival is after 12/11/2021 it will show as no price available yet

but prices could be added ad-hoc mid week so its not that i could say between 2 dates 1 week apart (if that makes sense)

 

[LGDGlen]

i guess what would be a better way to describe it would be that the price date in the table is the last arrival date the price is valid for

 

[theDBguy]

the arrival prices are any date from 30/10/2021 to 05/11/2021 should show a price of 5.50, anything up to 29/10/2021 should show 5.00 and anything from 06/11/2021 to 12/11/2021 should show price of 5.75, if an arrival is after 12/11/2021 it will show as no price available yet

but prices could be added ad-hoc mid week so its not that i could say between 2 dates 1 week apart (if that makes sense)

i guess what would be a better way to describe it would be that the price date in the table is the last arrival date the price is valid for

Hi. Thanks for the clarification. This could be one way to get the price.

DLookup("Price", "PriceTable", "Customer='Cust1' AND Fruit='Raspberries' AND UnitCount=20 AND UnitWeight=0.15 AND ArrivalDate<=#" & DMin("ArrivalDate", "PriceTable", "Customer='Cust1' AND Fruit='Raspberries' AND UnitCount=20 AND UnitWeight=0.15 AND ArrivalDate>=#02/11/2021#") & "#")

Obviously, you would concatenate the values in there. I just used literal values to make it simpler to see what you need to do.

Hope that helps...

 

[CJ_London]

the arrival prices are any date from 30/10/2021 to 05/11/2021 should show a price of 5.50

think you have some confusing (to me at any rate) terms. your arrival date in the price table would be better named as 'valid to' or similar

so you need to find the price record where that date is the earliest date later than the arrival date.

you could do this with a dmin function or a subquery - along these lines for dmin

dmin("arrivalddate","pricetable","[Customer] = '" & delsCustomer & "' AND " [Arrival Date] >= " & CSql(delsArrivalDate) & " AND [Fruit] = '" & delProdProduct & "' AND [Unit Count] = " & Me.del_prod_count & " AND [Unit Weight (KG)] = " & Me.del_prod_weight)

dmin will return null if no record is found

find first/last is meaningless without an order so if you are interrogating a recordset, make sure it is ordered at least by date

 

[LGDGlen]

@CJ_London awesome i'll give that a go, and yeah it all makes sense when you are doing it, and only when you try and describe it to someone you notice that the terms are a little confusing, yes the date is a "Valid to"

 

[CJ_London]

I see DBG has modified his suggestion (post#7) to incorporate mine, and has taken it to the next stage to return the price. So suggest take a look

 

[theDBguy]

I see DBG has modified his suggestion (post#7) to incorporate mine, and has taken it to the next stage to return the price. So suggest take a look

It was a work in progress.

 

[CJ_London]

 

[LGDGlen]

@theDBguy @CJ_London you guys are da best, thanks a million for the update, DMIN is a new one for me so now i'm aware of it at least in the future (hopefully) i'll remember and make use, i'll update this with "resolved" once i've implemented the solution and tested it out

 

[theDBguy]

@theDBguy @CJ_London you guys are da best, thanks a million for the update, DMIN is a new one for me so now i'm aware of it at least in the future (hopefully) i'll remember and make use, i'll update this with "resolved" once i've implemented the solution and tested it out

Hi. We're happy to assist. Let us know how it goes. Good luck!

 

[arnelgp]

sellPricesRS.FindFirst "[Customer] = '" & delsCustomer & "' AND " [Arrival Date] >= " & CSql(delsArrivalDate) & " AND [Fruit] = '" & delProdProduct & "' AND [Unit Count] = " & Me.del_prod_count & " AND [Unit Weight (KG)] = " & Me.del_prod_weight

 

[LGDGlen]

so after testing and checking etc it happens that the findfirst and dmin business was not returning the right results. and re-reading the above thread i think its because the "Valid To" dates are not ordered, they are all over the place.

find first/last is meaningless without an order so if you are interrogating a recordset, make sure it is ordered at least by date

findfirst (as i understand it) won't work with recordsets so i've gone with:

• Query the price table with all the criteria i have and the returned records are ordered in descending order based on date
• start at the first record and loop to the end of the records returned
  • if the date is greater than or equal to my arrival date
    • store a price and currency
    • flag that a price has been stored
  • end
• end loop
• if a price has been stored
  • update the form
• else
  • notify user that no price exists
• end if

seems to work everytime now so i hope thats the best way to do things, if not happy (again) to refactor the code to be better

 

[CJ_London]

because the "Valid To" dates are not ordered,

min is not affected by row order, findfirst is. So I suspect there is something else in play such as two records with the same data except for price

Seems to me you are using a very slow method of finding the price you want

 

[arnelgp]

findfirst (as i understand it) won't work with recordsets

that is the purpose of it, recordset.
you can index your table and open the recordset as Opentable, then use index to search.
see this demo:

 

[LGDGlen]

@CJ_London indeed, i assumed there would be a better way but it worked so i'm up and running for now

@arnelgp i will investigate your demo db and see what i need to do to get it working more efficiently using the database correctly and not via the vba i've used

 

[LGDGlen]

@arnelgp i have a question about the database example you have given. in the code for the button there is the following line:

 .Index = "srch_index"

i don't understand what this is doing here, can you explain what/where "srch_index" comes from please