Most Recent Receive Date for same Record

[kjacob318]

Hello,

I am trying to create a calculated field in my query to display the most recent receive date (other than the current OrderID) for a material and serial. Below is my table1 example:

[TABLE="width: 644"]
<tbody>[TR]
[TD]ORDERID[/TD]
[TD]MATERIAL[/TD]
[TD]SERIAL NUMBER[/TD]
[TD]CREATE DATE[/TD]
[TD]RECEIVE DATE[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]C123[/TD]
[TD]412[/TD]
[TD]1/1/2017[/TD]
[TD]3/3/2017[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]XYZF4[/TD]
[TD]128[/TD]
[TD]12/20/2016[/TD]
[TD]12/28/2016[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]C123[/TD]
[TD]412[/TD]
[TD]10/12/2016[/TD]
[TD]10/30/2016[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]ABYD3[/TD]
[TD]325[/TD]
[TD]9/10/2016[/TD]
[TD]9/25/2016[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]XYZF4[/TD]
[TD]128[/TD]
[TD]8/12/2016[/TD]
[TD]9/1/2016[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"></colgroup>[/TABLE]


What I want my result to be is:
[TABLE="width: 798"]
<tbody>[TR]
[TD]ORDERID[/TD]
[TD]MATERIAL[/TD]
[TD]SERIAL NUMBER[/TD]
[TD]CREATE DATE[/TD]
[TD]RECEIVE DATE[/TD]
[TD]MAX RECEIVE DATE[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]C123[/TD]
[TD]412[/TD]
[TD]1/1/2017[/TD]
[TD]3/3/2017[/TD]
[TD]10/30/2016[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]XYZF4[/TD]
[TD]128[/TD]
[TD]12/20/2016[/TD]
[TD]12/28/2016[/TD]
[TD]9/1/2016[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]C123[/TD]
[TD]412[/TD]
[TD]10/12/2016[/TD]
[TD]10/30/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]ABYD3[/TD]
[TD]325[/TD]
[TD]9/10/2016[/TD]
[TD]9/25/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]XYZF4[/TD]
[TD]128[/TD]
[TD]8/12/2016[/TD]
[TD]9/1/2016[/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col></colgroup>[/TABLE]


I am trying to do DMAX in my criteria for my query but it's not working. It's prompting me to type in "RECEIVE DATE" & "CREATE DATE".

DMax("[RECEIVE DATE]","TABLE1","[SERIAL NUMBER] = [SERIAL NUMBER] And [RECEIVE DATE] < #" & [CREATE DATE] & "#")

 

[xenou]

You cannot put the column names inside the quotes, or they become literal text. You want to create a string that includes the actual serial number. Assuming serial numbers are numeric.

DMax("[RECEIVE DATE]","TABLE1","[SERIAL NUMBER] = " & [SERIAL NUMBER] & " And [RECEIVE DATE] < #" & [CREATE DATE] & "#")
or
DMax("[RECEIVE DATE]","TABLE1","[SERIAL NUMBER] = " & [SERIAL NUMBER] & " AND [MATERIAL] = '" & [MATERIAL] & "'" & " And [RECEIVE DATE] < #" & [CREATE DATE] & "#")

Seems like you know this since you did it right for the date, just missed it for the serial number.

 

[kjacob318]

Awesome! Thank you! My Serial Numbers may not always be numerical but I included the "'" to mimic the Material and it worked perfectly!

I appreciate the help!