472,129 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,129 software developers and data experts.

How to automatically update last day of month before the newmonth

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
0 4737

Post your reply

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

Similar topics

2 posts views Thread by Vijay Balki | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.