469,342 Members | 5,948 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

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 4343

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 zhoujie | last post: by
1 post views Thread by haryvincent176 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.