Highlight Latest Records with respect to Date/Time

[RAJESHKUMAR R]

Dear Experts,



I have a Access Table contains “ProductID”,”UserInput”,”Updated Date/Time”,”Signal”

Each “ProductID” contains more number records (rows) with different Date/Time

Wherever the “Updated Date/Time” value = Max of (Updated Date/Time), The Signal column should get updated as “Latest Update” otherwise update as “Old Update”

Looking forward your input for the same…

Thanks & Regards,
Rajeshkumar R

[Welshgasman]

Use DMax() and compare against your date field.

Not sure you should be doing that though as you can calculate it easily with DMax()?

[orange]

Can't you just query the table to show the latest record? Or use a form with the query as its recordsource?
Sounds like SELECT TOP 1.... may be appropriate.

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition; 

[June7]

Saving aggregate calc is not advised. If it can be calculated for UPDATE it can be calculated when needed. Cannot use an aggregate query as source for an UPDATE action anyway. Would have to use domain aggregate function or VBA looping recordset.

[RAJESHKUMAR R]

Dear Welshgasman,

I already tried with below formula in calculated field!

=DMax("Updated Date/Time","table1","ProductID=" & "ProductID")

When I am trying to save the table, It is showing an error like "Unknown function 'DMAX' in validation expression or default value"

Please suggest where am I going wrong?

Thanks & Regards,
Rajeshkumar R

[June7]

The field name has space and special characters so that requires use of [] delimiters. Don't construct names that way and wouldn't have this issue.

Also, quote marks around concatenated field are not appropriate.

Your table is really named "table1"? ProductID is a number type?

=DMax("[Updated Date/Time]","table1","ProductID=" & ProductID)