i have invdate, nextdate(being the date after),days,
01/05/2016 03/05/2016 2days
03/05/2016 15/05/2016 12days
15/05/2016 26/05/2016 10 days
26/05/2016 i want to automaticall update here with 31/05/2016
02/06/2016
SELECT
Rental.OrderID, Rental.AccNo, Rental.Customer, Rental.InvDate, Rental.Invoice, Rental.QtyDel, Rental.QtyRet,
Sum(DSum("[QtyDel]-[QtyRet]","[Rental]","[Rental].[OrderID]=" & [Rental].[OrderID] & " AND [Rental].[OrderID]<=" & [Rental].[OrderID])) AS RT,
AllDays.InvDate,
Sum(DSum("[QtyDel]-[QtyRet]","[Rental]","[Rental].[OrderID]=" & [Rental].[OrderID] & " AND [Rental].[OrderID]<=" & [Rental].[OrderID])) AS DT,
AllDays.NextDate,
DateDiff("d",[AllDays].[InvDate],[NextDate]) AS Days
FROM
(SELECT
OrderID, AccNo, InvDate,
Nz((SELECT MIN(InvDate)
FROM Rental T2
WHERE T1.accno = T2.accno And T2.InvDate > T1.InvDate), DateSerial(Year(T1.InvDate), Month(T1.InvDate) + 1, 0)) AS NextDate
FROM
Rental AS T1) AS AllDays
INNER JOIN
Rental ON AllDays.OrderID=Rental.OrderID
GROUP BY
Rental.OrderID, Rental.AccNo, Rental.Customer, Rental.InvDate, Rental.Invoice, Rental.QtyDel, Rental.QtyRet,
AllDays.InvDate, AllDays.NextDate;