I'm attempting to have a query automatically update the criteria to the previous days date, I am unable to just replace the date with "Date()-1" as my oracle database does not recognize that syntax. So now my idea is to run the sql through vba and have another macro replace the date when it runs.
I have included my attempt at getting vba to run the sql, I currently am getting the error "Expected: end of statement" … I've tried correcting all of the issues to get this to run, but I have yet to be successful. Can someone point in the right direction so I can get this to run... and then figure out a way for VBA to update it.
I also underlined and colored the text in red the criteria I am trying to change.
DoCmd.SetWarnings False
Dim db As DAO.Database
Dim strQuery As String
strQuery = "SELECT [- Standard Calendar].[Day Date], [- Operational Organization Current Perspective Detail].[Region Num], [- Operational Organization Current Perspective Detail].[District Num], [- Operational Organization Current Perspective Detail].[Center Num], [- Package Pickup Detail].[Pickup Letterbox Source Num], [- Package Pickup Detail].[DIAD Orgnl Stop Acct Name], [- Package Pickup Detail].[Pickup Disposition Status Desc], [- Package Pickup Detail].[PU Info Status Reason Desc], [- Package Pickup Detail].[Pickup Letterbox Ind], [- Package Pickup Detail].[Pickup Disposition Status Cd], [- Stop - Pickup Acct Mgmnt Detail].[PAM Acct Num], [- Package Stop Basic Common Detail].[Stop Start Time], [- Package Stop Basic Common Detail].[Stop Complete Time], [- Package Stop Basic Common Detail].[Stop Complete Time HHMMSS], [- Stop Dispatch Timecard Detail].[DIAD Orgnl Pkg Driver Name] INTO Tbl_CutClosedCheck _
FROM [- Stop Pickup Detail] INNER JOIN (([- Package Stop Basic Common Detail] INNER JOIN (([- Operational Organization Current Perspective Detail] INNER JOIN ([- Standard Calendar] INNER JOIN [- Package Pickup Detail] ON [- Standard Calendar].[ODBC Join Key] = [- Package Pickup Detail].[ODBC Join Key]) ON [- Operational Organization Current Perspective Detail].[ODBC Join Key] = [- Package Pickup Detail].[ODBC Join Key]) INNER JOIN [- Stop - Pickup Acct Mgmnt Detail] ON [- Operational Organization Current Perspective Detail].[ODBC Join Key] = [- Stop - Pickup Acct Mgmnt Detail].[ODBC Join Key]) ON [- Package Stop Basic Common Detail].[ODBC Join Key] = [- Stop - Pickup Acct Mgmnt Detail].[ODBC Join Key]) INNER JOIN [- Stop Dispatch Timecard Detail] ON [- Package Stop Basic Common Detail].[ODBC Join Key] = [- Stop Dispatch Timecard Detail].[ODBC Join Key]) ON [- Stop Pickup Detail].[ODBC Join Key] = [- Standard Calendar].[ODBC Join Key] _
WHERE ((([- Standard Calendar].[Day Date])=#2/8/2019#) AND (([- Operational Organization Current Perspective Detail].[Region Num])="03")) OR ((([- Operational Organization Current Perspective Detail].[Region Num])="07")) OR ((([- Operational Organization Current Perspective Detail].[Region Num])="10") AND (([- Package Pickup Detail].[DIAD Orgnl Stop Acct Name]) Not Like "@@") AND (([- Package Pickup Detail].[Pickup Letterbox Ind])="1"));"
DoCmd.RunSQL strQuery