Formula help

[RC]

Have a query with 3 fields: ID, Inspect_Date, and
Last_Date. Last_Date is a calculated field that examines
all the dates listed in Inspect_Date, finds the latest
date, and then puts a "Y" in the column corresponding to
the last date. The results look like this:

ID Inspect_Date Last_Date
1 01/18/2003
1 01/19/2003
2 01/19/2003
2 01/20/2003
3 01/19/2003
3 01/21/2003
3 01/31/2003 Y

The formula that works to accomplish this is:
Last_Date: IIf(DMax("[Inspect_DATE]","INSPECTIONS")=
[Inspect_DATE],"Y","")

But what I need to find is the latest date FOR EACH ID
NUMBER, not just the latest date in the table. Here is the
result I am looking for:

ID Inspect_Date Last_Date
1 01/18/2003
1 01/19/2003 Y
2 01/19/2003
2 01/20/2003 Y
3 01/19/2003
3 01/21/2003
3 01/31/2003 Y

Does anyone know how to rewrite my formula to accomplish
this?

TIA,

RC

 

[Ken Snell]

Try this:

Last_Date: IIf(DMax("[Inspect_DATE]","INSPECTIONS", "[ID]=" & [ID])=
[Inspect_DATE],"Y","")

 

[Fredg]

Add a Where clause to the DMax() function.
Air code:
See if this works....

Last_Date: IIf(DMax("[Inspect_DATE]","INSPECTIONS","[ID] = " &
[ID])=[Inspect_DATE] ,"Y","")

 

[RC]

Ken & Fred - Thanks guys, your solution works perfectly!
RC