I am a novice user and have only recently started using VBA in Access. I have been tasked to change a complex database to reflect a new date period for a month. Instead of using the beginning of the month as 1, my customer wants the month to be from the 23rd to the 22nd. This will give them a week to prepare for processing.
I have managed to get most of it working, with the following exception. I need to assign DT according to the new criteria.
While the way it was developed is far from the method I would use. I am stuck with making it work.
Here is the old code in SQL view:
SELECT G.Part, G.Process, Sum(G.QPass2) AS QtyPass, Sum(G.QFail2) AS QtyFail, Sum(G.QNull2) AS QtyNull, IIf(Sum(SYtd)=0,NULL,Sum(SYtd)) AS Sprayed_Yesterday, Sum(G.SpMTD) AS SprayedMTD, G.AftMkt, G.DT FROM (SELECT Sprayed.Part, Sprayed.Process, Sum(Sprayed.QPass) AS QPass2, Sum(Sprayed.QFail) AS QFail2, Sum(Sprayed.QNull) AS QNull2, Sum(IIF(Sprayed.Date_Stamp = Date()-1,Sprayed.QPass + Sprayed.QFail + Sprayed.QNull,0)) AS SYtd, Sum(Sprayed.Qpass + Sprayed.QFail + Sprayed.QNull) AS SpMTD, Sprayed.AftMkt, Dateserial(Year(Date_Stamp), Month(Date_Stamp), 1) AS DT FROM Sprayed GROUP BY Part, Process, AftMkt, Dateserial(Year(Date_Stamp), Month(Date_Stamp), 1)) AS G GROUP BY G.Part, G.Process, G.AftMkt, G.DT HAVING (((Sum([G].[QPass2])+Sum([G].[QFail2])+Sum([SYtd]))>0));