How to troubleshoot SQL query within VBA code?

Question

I have made a SQL statement that calculates the sum of the chosen fields I've gathered. I have the queries working within MS Access, but when I translate it to VBA coding within my database it spits out a compile error: Syntax error. Below I have attached my working query as well as my query with the syntax.

Query within MS Access is below that works properly:

SELECT 1,'Passed - Depot' AS QRY, Sum(IIf(([PreStressStackDate]>=[StartDate] And [PreStressStackDate]<=[EndDate]) And (([CurrentLevelOfCompletion]>=5 And [CurrentLevelOfCompletion]<1073741829) Or [CurrentLevelOfCompletion]>1073741829),1,0)) AS [PreStress Stackup], Sum(IIf(([StackCompressionDate]>=[StartDate] And [StackCompressionDate]<=[EndDate]) And (([CurrentLevelOfCompletion]>=21 And [CurrentLevelOfCompletion]<1073741845) Or [CurrentLevelOfCompletion]>1073741845),1,0)) AS [Stack Compression], Sum(IIf(([TestingDate]>=[StartDate] And [TestingDate]<=[EndDate]) And (([CurrentLevelOfCompletion]>85 And [CurrentLevelOfCompletion]<1073741909) Or [CurrentLevelOfCompletion]>1073741909),1,0)) AS Testing, Sum(IIf(([ShroudAssemblyDate]>=[StartDate] And [ShroudAssemblyDate]<=[EndDate]) And (([CurrentLevelOfCompletion]>=341 And [CurrentLevelOfCompletion]<1073742165) Or [CurrentLevelOfCompletion]>1073742165),1,0)) AS [Shroud Assembly], Sum(IIf(([TransformerInstallDate]>=[StartDate] And [TransformerInstallDate]<=[EndDate]) And (([CurrentLevelOfCompletion]>=1365 And [CurrentLevelOfCompletion]<1073743189)),1,0)) AS [Transformer Installation]
FROM TR343DrySide
WHERE (([TransducerSN] Not Like "CR*"));

UNION SELECT 2, 'Failed - Depot' AS QRY, Sum(IIf(([PreStressStackDate]>=[StartDate] And [PreStressStackDate]<=[EndDate]) And [CurrentLevelOfCompletion]=1073741829,1,0)) AS [PreStress Stackup], Sum(IIf(([StackCompressionDate]>=[StartDate] And [StackCompressionDate]<=[EndDate]) And [CurrentLevelOfCompletion]=1073741845,1,0)) AS [Stack Compression], Sum(IIf(([TestingDate]>=[StartDate] And [TestingDate]<=[EndDate]) And [CurrentLevelOfCompletion]=1073741909,1,0)) AS [Testing], Sum(IIf(([ShroudAssemblyDate]>=[StartDate] And [ShroudAssemblyDate]<=[EndDate]) And [CurrentLevelOfCompletion]=1073742165,1,0)) AS [Shroud Assembly], Sum(IIf(([TransformerInstallDate]>=[StartDate] And [TransformerInstallDate]<=[EndDate]) And [CurrentLevelOfCompletion]=1073743189,1,0)) AS [Transformer Installation]
FROM TR343DrySide
WHERE (([TransducerSN] Not Like "CR*"));

When this query is run, it tallies up the sum of the fields between the chosen dates.

Below I have attached my VBA code that comes up with a syntax compile error:

Private Sub cmdDrySideRunReport_Click()

    Dim strDrySQL_New, strDrySQL_Depot       As String
    Dim DryStartDate                         As Date
    Dim DryEndDate                           As Date


  '------------------------------------------------------------------------------------------------------
  If IsNull(Me.txtDryStartDate) Or Me.txtDryStartDate = "" Or IsNull(Me.txtDryEndDate) Or Me.txtDryEndDate = "" Then
        If IsNull(Me.txtDryStartDate) Or Me.txtDryStartDate = "" Then
            MsgBox "Please enter the Start Date"
            Me.txtDryStartDate.SetFocus
        End If
        If IsNull(Me.txtDryEndDate) Or Me.txtDryEndDate = "" Then
            MsgBox "Please enter the End Date"
            Me.txtDryEndDate.SetFocus
        End If
    Else
        DryStartDate = Me.txtDryStartDate
        DryEndDate = Me.txtDryEndDate + 1



        '###########################################################3
        'DRYSIDE NEW


        strDrySQL_New = "Select 1, 'Passed - New' AS QRY, Sum(IIf(([PreStressStackDate]>=#" & DryStartDate & "# And [PreStressStackDate]<=#" & DryEndDate & "#)" & _
                        " And (([CurrentLevelOfCompletion]>=5 And [CurrentLevelOfCompletion]<1073741829) Or [CurrentLevelOfCompletion]>1073741829),1,0)) AS [PreStress Stackup]," & _
                        " Sum(IIf(([StackCompressionDate]>=#" & DryStartDate & "# And [StackCompressionDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=21" & _
                        " And [CurrentLevelOfCompletion]<1073741845) Or [CurrentLevelOfCompletion]>1073741845),1,0)) AS [Stack Compression]," & _
                        " Sum(IIf(([TestingDate]>=#" & DryStartDate & "# And [TestingDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=85" & _
                        vbCrLf & " And [CurrentLevelOfCompletion]<1073741909) Or [CurrentLevelOfCompletion]>1073741909),1,0)) AS [Testing]," & _
                        " Sum(IIf(([ShroudAssemblyDate]>=#" & DryStartDate & "# And [ShroudAssemblyDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=341" & _
                        " And [CurrentLevelOfCompletion]<1073742165) Or [CurrentLevelOfCompletion]>1073742165),1,0)) AS [Shroud Assembly]," & _
                        " Sum(IIf(([TransformerInstallDate]>=#" & DryStartDate & "# And [TransformerInstallDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=1365 And [CurrentLevelOfCompletion]<1073743189)),1,0)) AS [Transformer Installation]" & _
                        " FROM TR343DrySide" & _
                        " WHERE (([TransducerSN] Like ""CR*"")) " & _
                        vbCrLf & " UNION SELECT 2, 'Failed - New' AS QRY, Sum(IIf(([PreStressStackDate]>=#" & StartDate & "#" & _
                        " And [PreStressStackDate]<=#" & EndDate & "#) And [CurrentLevelOfCompletion]=1073741829,1,0)) AS [PreStress Stackup]," & _
                        " Sum(IIf(([StackCompressionDate]>=#" & StartDate & "# And [StackCompressionDate]<=#" & EndDate & "#) And [CurrentLevelOfCompletion]=1073741845,1,0)) AS [Stack Compression]," & _
                        " Sum(IIf(([TestingDate]>=#" & StartDate & "# And [TestingDate]<=#" & EndDate & "#) &" _
                        " And [CurrentLevelOfCompletion]=1073741909,1,0)) AS [Testing], Sum(IIf(([ShroudAssemblyDate]>=#" & StartDate & "# And [ShroudAssemblyDate]<=#" & EndDate & "#)" & _
                        " And [CurrentLevelOfCompletion]=1073742165,1,0)) AS [Shroud Assembly], Sum(IIf(([TransformerInstallDate]>=#" & StartDate & "# And [TransformerInstallDate]<=#" & EndDate & "#)" & _
                        " And [CurrentLevelOfCompletion]=1073742165,1,0)) AS [Transformer Installation]" & _
                        " FROM TR343DrySide" & _
                        " WHERE (([TransducerSN] Like ""CR*""));"







        Me.sfrmCraneDrySidePassFailDateRange_New.Form.RecordSource = strDrySQL_New
        Me.sfrmCraneDrySidePassFailDateRange_New.Visible = True

        '###########################################################
        'DRYSIDE DEPOT
        strDrySQL_Depot = "Select 1, 'Passed - New' AS QRY, Sum(IIf(([PreStressStackDate]>=#" & DryStartDate & "# And [PreStressStackDate]<=#" & DryEndDate & "#)" & _
                        " And (([CurrentLevelOfCompletion]>=5 And [CurrentLevelOfCompletion]<1073741829) Or [CurrentLevelOfCompletion]>1073741829),1,0)) AS [PreStress Stackup]," & _
                        " Sum(IIf(([StackCompressionDate]>=#" & DryStartDate & "# And [StackCompressionDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=21" & _
                        " And [CurrentLevelOfCompletion]<1073741845) Or [CurrentLevelOfCompletion]>1073741845),1,0)) AS [Stack Compression]," & _
                        " Sum(IIf(([TestingDate]>=#" & DryStartDate & "# And [TestingDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=85" & _
                        vbCrLf & " And [CurrentLevelOfCompletion]<1073741909) Or [CurrentLevelOfCompletion]>1073741909),1,0)) AS [Testing]," & _
                        " Sum(IIf(([ShroudAssemblyDate]>=#" & DryStartDate & "# And [ShroudAssemblyDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=341" & _
                        " And [CurrentLevelOfCompletion]<1073742165) Or [CurrentLevelOfCompletion]>1073742165),1,0)) AS [Shroud Assembly]," & _
                        " Sum(IIf(([TransformerInstallDate]>=#" & DryStartDate & "# And [TransformerInstallDate]<=#" & DryEndDate & "#) And (([CurrentLevelOfCompletion]>=1365 And [CurrentLevelOfCompletion]<1073743189)),1,0)) AS [Transformer Installation]" & _
                        " FROM TR343DrySide" & _
                        " WHERE (([TransducerSN] Not Like ""CR*""));"

        Me.sfrmCraneDrySidePassFailDateRange_Depot.Form.RecordSource = strDrySQL_Depot
        Me.sfrmCraneDrySidePassFailDateRange_Depot.Visible = True
    End If
End Sub

I believe that the error occurs within this statement:

  "Sum(IIf(([TestingDate]>=#" & StartDate & "# And [TestingDate]<=#" & EndDate & "#) &" _
                        " And [CurrentLevelOfCompletion]=1073741909,1,0)) AS [Testing], Sum(IIf(([ShroudAssemblyDate]>=#" & StartDate & "# And [ShroudAssemblyDate]<=#" & EndDate & "#)" & _
                        " And [CurrentLevelOfCompletion]=1073742165,1,0)) AS [Shroud Assembly],"

error 3319 within my union statement. my thought is that I am missing some sort of punctuation, but cannot put my finger on where... — nick irvin, Nov 20 '18 at 14:23
Your error line is the `" And [CurrentLevelOfCompletion]=1073741909,1,0)) AS [Testing]"` has the closing `"` in and with `[Shroud Assembly]"` and closing `"` round this `Sum(IIf(([TestingDate]>=#" & StartDate & "# And [TestingDate]<=#" & EndDate & "#)"` — Nathan_Sav, Nov 20 '18 at 14:23
@nickirvin, if you encounter an error, always post *at least* the error **message**. Nobody knows all error codes of all systems by heart. — HoneyBadger, Nov 20 '18 at 14:25
so how should I jeep my statement open? just remove The " at the end? Thank you — nick irvin, Nov 20 '18 at 14:26
apologies @HoneyBadger. I completely spaced on the error when i entered in my question. — nick irvin, Nov 20 '18 at 14:27
Sql Injection warning https://xkcd.com/327/ use parametrized query — Juan Carlos Oropeza, Nov 20 '18 at 14:28
Also would be easier if you show the `Debug.Print strSQL` so we can see the final query — Juan Carlos Oropeza, Nov 20 '18 at 14:29
.......and with `[Shroud Assembly]"` and closing `"` round this `Sum(IIf(([TestingDate]>=#" & StartDate & "# And [TestingDate]<=#" & EndDate & "#)"` When using long strings like this, it's best to debug like so `s="first part of string"` `s=s&"secondpart"` and so on. — Nathan_Sav, Nov 20 '18 at 14:29
or as @JuanCarlosOropeza says debug.? the SQL to inspect and possible try to execute it as well in query editor or such like — Nathan_Sav, Nov 20 '18 at 14:34
@JuanCarlosOropeza decided that debug.print strSQL was not needed and commented out. need to look into Parameterized Queries. I do not believe I have used that before... — nick irvin, Nov 20 '18 at 14:35
Well you have an error on the query you dont show us the query. Instead we have to build the query reading your code. That isnt an efficient task. Just edit the question and add the final query — Juan Carlos Oropeza, Nov 20 '18 at 14:36
This may help with the parametrized query https://stackoverflow.com/questions/7472563/passing-parameter-to-query-for-access-database also remove a few string concatenations — Juan Carlos Oropeza, Nov 20 '18 at 14:43
@JuanCarlosOropeza That's VB.Net, not VBA, won't work. See [this answer](https://stackoverflow.com/a/49509616/7296893) for an overview of the different approaches in VBA. — Erik A, Nov 20 '18 at 14:47
Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) Focus on the minimal part. we dont need all that code just the query part to help you solve the sintaxis error — Juan Carlos Oropeza, Nov 20 '18 at 14:54

ArcherBird · Answer 1 · 2018-11-20T14:48:57.757

Your String is no good, and becomes invalid on these lines with a rogue " in your string:

" And [CurrentLevelOfCompletion]=1073741909,1,0)) AS [Testing]", Sum(IIf(([ShroudAssemblyDate]>=#" & StartDate & "# And [ShroudAssemblyDate]<=#" & EndDate & "#)" & _

" And [CurrentLevelOfCompletion]=1073742165,1,0)) AS [Shroud Assembly]", Sum(IIf(([TransformerInstallDate]>=#" & StartDate & "# And [TransformerInstallDate]<=#" & EndDate & "#)" & _

MultiLine string assignments like this are supposed to be used to make code more readable. In your case, I think it is making things less readable and harder to troubleshoot. I would recommend building a query object out of this sql instead of building the string in VBA. For troubleshooting purposes ONLY, If you must build it in vba - use more structured, self-concatenating way of building the string. Its a lot easier to see the compile error this way.

strDrySQL_New = "SELECT 1 " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,'Passed - New' AS QRY " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [PreStressStackDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [PreStressStackDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   [CurrentLevelOfCompletion] >= 5 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   AND [CurrentLevelOfCompletion] < 1073741829 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "               OR [CurrentLevelOfCompletion] > 1073741829 " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ), 1, 0)) AS [PreStress Stackup] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [StackCompressionDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [StackCompressionDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   [CurrentLevelOfCompletion] >= 21 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   AND [CurrentLevelOfCompletion] < 1073741845 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "               OR [CurrentLevelOfCompletion] > 1073741845 " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ), 1, 0)) AS [Stack Compression] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [TestingDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [TestingDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   [CurrentLevelOfCompletion] >= 85 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   AND [CurrentLevelOfCompletion] < 1073741909 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "               OR [CurrentLevelOfCompletion] > 1073741909 " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ), 1, 0)) AS [Testing] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [ShroudAssemblyDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [ShroudAssemblyDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   [CurrentLevelOfCompletion] >= 341 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   AND [CurrentLevelOfCompletion] < 1073742165 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "               OR [CurrentLevelOfCompletion] > 1073742165 " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ), 1, 0)) AS [Shroud Assembly] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [TransformerInstallDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [TransformerInstallDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ( " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   [CurrentLevelOfCompletion] >= 1365 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   AND [CurrentLevelOfCompletion] < 1073743189 " & vbCrLf
strDrySQL_New = strDrySQL_New & "                   ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ), 1, 0)) AS [Transformer Installation] " & vbCrLf
strDrySQL_New = strDrySQL_New & "FROM TR343DrySide " & vbCrLf
strDrySQL_New = strDrySQL_New & "WHERE (([TransducerSN] LIKE ""CR*"")) " & vbCrLf
strDrySQL_New = strDrySQL_New & " " & vbCrLf
strDrySQL_New = strDrySQL_New & "UNION " & vbCrLf
strDrySQL_New = strDrySQL_New & " " & vbCrLf
strDrySQL_New = strDrySQL_New & "SELECT 2 " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,'Failed - New' AS QRY " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [PreStressStackDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [PreStressStackDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND [CurrentLevelOfCompletion] = 1073741829, 1, 0)) AS [PreStress Stackup] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [StackCompressionDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [StackCompressionDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND [CurrentLevelOfCompletion] = 1073741845, 1, 0)) AS [Stack Compression] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [TestingDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [TestingDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND [CurrentLevelOfCompletion] = 1073741909, 1, 0)) AS [Testing] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [ShroudAssemblyDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [ShroudAssemblyDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND [CurrentLevelOfCompletion] = 1073742165, 1, 0)) AS [Shroud Assembly] " & vbCrLf
strDrySQL_New = strDrySQL_New & "   ,Sum(IIf(( " & vbCrLf
strDrySQL_New = strDrySQL_New & "               [TransformerInstallDate] >= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               AND [TransformerInstallDate] <= ## " & vbCrLf
strDrySQL_New = strDrySQL_New & "               ) " & vbCrLf
strDrySQL_New = strDrySQL_New & "           AND [CurrentLevelOfCompletion] = 1073742165, 1, 0)) AS [Transformer Installation] " & vbCrLf
strDrySQL_New = strDrySQL_New & "FROM TR343DrySide " & vbCrLf
strDrySQL_New = strDrySQL_New & "WHERE (([TransducerSN] LIKE ""CR*"")); " & vbCrLf

The above code will actually debug.print a nicely formatted SQL statement; Much easier to troubleshoot

Thank you very much for the advice. unfortunately, the DB has been built in VBA, and certain functions within the program must be in VBA. I will look into self-concatenating my statements. First time really coding within VBA and am a bit lost on certain subjects, many thanks. — nick irvin, Nov 20 '18 at 14:38
Note that swapping multiline statements for concatenation can have a serious performance impact. Instead of allocating memory and copying the string to memory once, you're allocating memory, copying the string, allocating a new larger part of memory, copying the string over, appending a new part of the string, deallocating the old string, times 20. Imo you're advocating a bad practice, this is precisely why other languages have string builders, because the performance impact is not negligible. — Erik A, Nov 20 '18 at 14:44
@ErikvonAsmuth is absolutely correct. In your final code, this is not what you should do. BUT, this will help you find the place in the string where you've made a syntax error. It is much better to have a way to build the string all at once, or better yet - import from a text file, or as I said, use a queryDef object — ArcherBird, Nov 20 '18 at 14:47
@ErikvonAsmuth Thank you. I have updated my question to give you more detail into what is going on. — nick irvin, Nov 20 '18 at 14:49
@ArcherBird – you can get nicer, shorter and faster code using line continuation using `_` at end of line: `strDrySQL_New = "SELECT 1 " & vbCrLf _ ↵ & " ,'Passed - New' AS QRY " & vbCrLf _ ↵ & " ,Sum(IIf(( " & vbCrLf _ ↵ ...` where the symbol `↵` means actual line break. See https://stackoverflow.com/questions/16624550/how-to-break-long-string-to-multiple-lines — miroxlav, Nov 20 '18 at 15:23
@miroxlav absolutely correct. This is what the OP had done originally, but couldn't find the syntax error in the string. This suggestion is only for troubleshooting where in the string the error was made (as the compiler will complain about the specific line of the string, instead of the entire string) — ArcherBird, Nov 20 '18 at 15:56

How to troubleshoot SQL query within VBA code?

1 Answers1