Results 1 to 6 of 6
  1. #1
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23

    Trying to update SQL with VBA

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why VBA when queries are so much easier?

    Dateadd("d",-1,Date) is also a way to alter 1 day.

  3. #3
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    I've tried that, my oracle server does not play nice with those.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oracle shouldn't care about the Date() function since the query is being run within Access. A passthrough query would require Oracle syntax. What does the finished SQL look like?

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    this seems to be a continuation of this thread https://www.accessforums.net/showthread.php?t=75601

    suspect the issue is the string is not continued

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

    Also your double quotes within a string should be single quotes

    Not Like "@@") AND (([- Package Pickup Detail].[Pickup Letterbox Ind])="1"));"

  6. #6
    UPSDuder is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2019
    Posts
    23
    Yes, it is a continuation... I'm trying to find my workaround..

    I will try your suggestions to see if I can get the SQL to run.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-21-2018, 03:16 AM
  2. Replies: 2
    Last Post: 08-03-2017, 04:58 AM
  3. Replies: 6
    Last Post: 11-05-2014, 05:27 PM
  4. Replies: 7
    Last Post: 08-26-2013, 06:05 AM
  5. Replies: 1
    Last Post: 01-22-2011, 12:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums