By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,405 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,405 IT Pros & Developers. It's quick & easy.

How to automatically update last day of month before the newmonth

P: 1
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;
Jun 9 '16 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.