How can I query to find the most current date in a list of dates?

[Guest]

For example I want to find the last date a product (say: MS101) was sold.

Product Date Sold
MS101 2/12/2005
MS101 3/18/2005
MS101 9/20/2005

I want my query to find the last date MS101 was sold (showing me 9/20/2005).
I have tried using LAST, FIRST, MAX and none of them are giving me the most
current date.

Thanks in advance for any help

 

[Guest]

Two questions:
Are you only worried about product MS101 or the last date ALL products were
sold?
Is the Date Sold field an actual Date/Time datatype or just a text field
holding what looks like a date?

Max should have worked. First and Last are about useless so try to avoid
them. If you just want the latest date for a particular Product, something
like either of these two should work if Date Sold is in a date field.

SELECT Product, Max([Date Sold])
FROM YourTableName
GROUP BY product
HAVING Product = "MS101" ;

or

SELECT TOP 1 Product, [Date Sold]
FROM YourTableName
WHERE Product = "MS101"
ORDER BY [Date Sold] Desc;

 

[Marshall Barton]

For example I want to find the last date a product (say: MS101) was sold.

Product Date Sold
MS101 2/12/2005
MS101 3/18/2005
MS101 9/20/2005

I want my query to find the last date MS101 was sold (showing me 9/20/2005).
I have tried using LAST, FIRST, MAX and none of them are giving me the most
current date.

Max should do what you want:

SELECT Product
Max([Date Sold]) As Latest
FROM yourtable
WHERE Product = "MS101"
GROUP BY Product