Nov 21 2005, 10:24 PM
My table has name and date fields. Both are text fields. There can be multiple dates for a name. I need to write a query to return the most recent date for each name, with any null value date counting as the highest date.
I managed to sort the data in the table by date, with null values first, using 2 queries:
SELECT * from myTable;
SELECT * FROM myQuery ORDER BY IIf([Date] Is Null,99999,Val([Date])) DESC;
Can I do something similar to get just the most recent date for each name, with any null values as the most recent date, using an aggregate or other function?
Nov 22 2005, 06:11 AM
I don't see what your first query adds to the process.
This query gives you the initial answer.
SELECT namefield, max( IIf([Date] Is Null,99999,clng([Date]))) as Seq FROM mytable Group BY namefield
Nov 22 2005, 07:38 AM
Further refine the query by using the Nz() function instead of the IIf(), and don't use Date as the name for a field it is a Reserved word and will cause you troubles.
SELECT namefield, Max(Nz(dtmDate,Date()) as Seq FROM mytable Group BY namefield
Nov 22 2005, 06:46 PM
Thank you both.
Nov 22 2005, 06:51 PM
You are welcome.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here