NextDueDate problem

  • Thread starter J B via AccessMonster.com
  • Start date
J

J B via AccessMonster.com

I have a form where NextDueDate is calculated from, =dateadd(“m”,3,
[CompletedDate]), CompletedDate but I want to delete past entries of
NextDueDate whenever a new NextDueDate is calculated I know this has to be a
simple fix.
 
A

Allen Browne

You want to return only the most recent service for each vehicle(?), and
when it is due again?

Use a subquery to select only the most recent service.
The query will look something like this:

SELECT Service.*, CVDate(DateAdd("m", 3, [CompletedDate])) AS NextDueDate
FROM Service
WHERE Service.ID =
(SELECT TOP 1 ID FROM Service AS Dupe
WHERE Dupe.VehicleID = Service.VehicleID
ORDER BY Dupe.CompletedDate, Dupe.ID);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Top