last() equivalent problem

[kalto]

Hi, i'm currently migrating an access database to an Access Project and i'm trying to achieve this in SQL Server:

SELECT dbProductionCard.ProductID, Last(dbProductionCard.ClosingDate) AS LastOfClosingDate
FROM dbProductionCard
GROUP BY dbProductionCard.ProductID, dbProductionCard.Completed, dbProductionCard.TransfertToInventory
HAVING (((dbProductionCard.Completed)=True) AND ((dbProductionCard.TransfertToInventory)=True));

It returns a list of products and the last closing date for each.

Except for the last() it's quite easy, but i can't find a way around that last(). I tried this:

DECLARE @LastOfClosingDate varchar(50)
SET @LastOfClosingDate = (SELECT TOP 1 dbProductionCard.ClosingDate FROM dbProductionCard ORDER BY CardNumberID DESC)

SELECT dbProductionCard.ProductID, @LastOfClosingDate AS LastOfClosingDate
FROM dbProductionCard
GROUP BY dbProductionCard.ProductID, dbProductionCard.Completed, dbProductionCard.TransfertToInventory
HAVING (((dbProductionCard.Completed)=1) AND ((dbProductionCard.TransfertToInventory)=1))

But it returns only the first ClosingDate of the table, not grouping with the ProductID.

So, any1 got an idea? Thx for your time.

 

[cmmrfrds]

change last to max.

 

[kalto]

Because it's the last row doesn't mean it has the highest value...