MOST RECENT VALUE OF SPECIFIC CRITERA

[Shafique]

I have an order table named tlb_ORDERS.
ID..........ODATE........... ITEMNAME...........ITEMTYPE..........RATE
1.............20-07-23.......PIPE..........................PLASTIC.............450
2.............20-07-23.......DOOR......................PVC......................980
3.............21-07-23.......PIPE..........................PLASTIC..............480

here i have a PIPE order again.
i want in my Order form when i post PIPE order it shows the most recent rate of ITEMNAME+ITEMTYPE Criteria

 

[welshgasman]

If you are using a combo for ItemName, then bring in your other fields into the source and refer to them with the respective column number. Combos start at 0.
If not use DlookUp().

 

[Micron]

Not clear to me. Post title says most recent, single example indicates the largest number is wanted. They are not necessarily the same thing.
If the largest, perhaps DMax of Rate field with criteria for ItemType and ItemName. If the price goes down that probably isn't what is wanted. In that case, you could use Max of ID in a query to get just the ID and use that in a DLookup. Or DMax on the table as before but again, to get only the ID to be used in the lookup.

 

[welshgasman]

I would create a query with Max(Date) and Group by ITEMNAME and ITEMTYPE.

Then use a DlookUp() on that query.
hHat way you should get 480 for 21/07/23

If you can have Pipe Metal, it would need ITEMNAME+ITEMTYPE as a calculated field and use that as criteria.

 

[Shafique]

Not clear to me. Post title says most recent, single example indicates the largest number is wanted. They are not necessarily the same thing.
If the largest, perhaps DMax of Rate field with criteria for ItemType and ItemName. If the price goes down that probably isn't what is wanted. In that case, you could use Max of ID in a query to get just the ID and use that in a DLookup. Or DMax on the table as before but again, to get only the ID to be used in the lookup.

We buy different items again and again and each time the price of the same item is different. I want to know the previous price when buying the same item again.
As the plastic pipe was first bought for 450 then the same pipe was bought for 480. So when I post the plastic pipe order again, it shows the previous price of the item (480).
in my new order report i use it in query this way.
LASTRATE: DLookUp("IRATE","FPORDER","[INAME] ='" & [FPORDER].[INAME] & "' AND [ITYPE]='" & [FPORDER].[ITYPE] & "' AND [ISIZE] ='" & [FPORDER].[ISIZE] & "'AND [ID]=" & [ID] & "")

my table name is [FORDER]

 

[Shafique]

Not clear to me. Post title says most recent, single example indicates the largest number is wanted. They are not necessarily the same thing.
If the largest, perhaps DMax of Rate field with criteria for ItemType and ItemName. If the price goes down that probably isn't what is wanted. In that case, you could use Max of ID in a query to get just the ID and use that in a DLookup. Or DMax on the table as before but again, to get only the ID to be used in the lookup.

i tried several ways as you said with DMAX
LASTRATE: DLookUp("IRATE","FPORDER","[INAME] =" & [FPORDER].[INAME] & " AND [ITYPE]=" & [FPORDER].[ITYPE] & " AND [ISIZE] =" & [FPORDER].[ISIZE] & "AND [PODATE]<#" & DMax("podate","fporder") & "#")
now it comes with this error

 

[welshgasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
Then when correct, I can use that in the function.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

TBH I think you would need to findthe PK for the last date for a product, then find the price with that PK .
If I was doing this I would create a function, as it sounds like something you need all the time, in which case I would use a recordset and get all the info in one pass.

 

[Shafique]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
Then when correct, I can use that in the function.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

TBH I think you would need to findthe PK for the last date for a product, then find the price with that PK .
If I was doing this I would create a function, as it sounds like something you need all the time, in which case I would use a recordset and get all the info in one pass.

There is also a problem that I don't know how to debug.print

 

[welshgasman]

There is also a problem that I don't know how to debug.print

Well that is very easy.

In your code you add
Debug.Print strCriteria

where strCriteria is the criteria for your domain function.
That will then appear in the Immediate Window (Ctrl + G)
You can use MSGBOX instead, but gets annoying (at least to me after a few OKs)

If you are going to stick with Access, you need to learn the basics.
Have a look at these videos. https://www.youtube.com/results?search_query=debug+access+vba