Create a date field in SQL query for Access based on criteria


Keywords:ms  access 


Question: 

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));

2 Answers: 

Replace the formula for DT with this: IIf(Day(Date_Stamp) > 22, DateSerial(Year(Date_Stamp), Month(Date_Stamp)+1, 1), DateSerial(Year(Date_Stamp), Month(Date_Stamp), 1))



You can simply offset the Date_Stamp by one week:

Dateserial(Year(DateAdd("ww", 1, Date_Stamp)), Month(DateAdd("ww", 1, Date_Stamp)), 1) AS DT