How to troubleshoot SQL query within VBA code?

0

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],"

sql vba ms-access

share|improve this question

edited Nov 20 '18 at 21:45

halfer

14.4k758110

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's the error?
  
  – Nathan_Sav
  Nov 20 '18 at 14:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Sql Injection warning xkcd.com/327 use parametrized query
  
  – Juan Carlos Oropeza
  Nov 20 '18 at 14:28
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

 | 
show 11 more comments

0

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],"

sql vba ms-access

share|improve this question

edited Nov 20 '18 at 21:45

halfer

14.4k758110

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's the error?
  
  – Nathan_Sav
  Nov 20 '18 at 14:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Sql Injection warning xkcd.com/327 use parametrized query
  
  – Juan Carlos Oropeza
  Nov 20 '18 at 14:28
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

 | 
show 11 more comments

0

0

0

0

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],"

sql vba ms-access

share|improve this question

edited Nov 20 '18 at 21:45

halfer

14.4k758110

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

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],"

sql vba ms-access

sql vba ms-access

share|improve this question

edited Nov 20 '18 at 21:45

halfer

14.4k758110

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

share|improve this question

edited Nov 20 '18 at 21:45

halfer

14.4k758110

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

share|improve this question

share|improve this question

edited Nov 20 '18 at 21:45

halfer

14.4k758110

edited Nov 20 '18 at 21:45

halfer

14.4k758110

edited Nov 20 '18 at 21:45

halfer

14.4k758110

14.4k758110

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

asked Nov 20 '18 at 14:20

nick irvinnick irvin

12

12

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's the error?
  
  – Nathan_Sav
  Nov 20 '18 at 14:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Sql Injection warning xkcd.com/327 use parametrized query
  
  – Juan Carlos Oropeza
  Nov 20 '18 at 14:28
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

 | 
show 11 more comments

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What's the error?
  
  – Nathan_Sav
  Nov 20 '18 at 14:21
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Sql Injection warning xkcd.com/327 use parametrized query
  
  – Juan Carlos Oropeza
  Nov 20 '18 at 14:28
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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
  
  
  

  
  
  
  

What's the error?

– Nathan_Sav
Nov 20 '18 at 14:21

What's the error?

– Nathan_Sav
Nov 20 '18 at 14:21

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

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

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

1

1

Sql Injection warning xkcd.com/327 use parametrized query

– Juan Carlos Oropeza
Nov 20 '18 at 14:28

Sql Injection warning xkcd.com/327 use parametrized query

– Juan Carlos Oropeza
Nov 20 '18 at 14:28

1

1

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

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

 | 
show 11 more comments

1 Answer
1

active

oldest

votes

1

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

share|improve this answer

edited Nov 20 '18 at 14:48

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 von Asmuth
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

 | 
show 1 more comment

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53395044%2fhow-to-troubleshoot-sql-query-within-vba-code%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

active

oldest

votes

active

oldest

votes

1

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

share|improve this answer

edited Nov 20 '18 at 14:48

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 von Asmuth
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

 | 
show 1 more comment

1

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

share|improve this answer

edited Nov 20 '18 at 14:48

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 von Asmuth
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

 | 
show 1 more comment

1

1

1

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

share|improve this answer

edited Nov 20 '18 at 14:48

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

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

share|improve this answer

edited Nov 20 '18 at 14:48

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

share|improve this answer

share|improve this answer

edited Nov 20 '18 at 14:48

edited Nov 20 '18 at 14:48

edited Nov 20 '18 at 14:48

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

answered Nov 20 '18 at 14:34

ArcherBirdArcherBird

773219

773219

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 von Asmuth
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

 | 
show 1 more comment

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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 von Asmuth
  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
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @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
  

  
  
  
  

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

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

1

1

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 von Asmuth
Nov 20 '18 at 14:44

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 von Asmuth
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 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

@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

1

1

@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

@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

 | 
show 1 more comment

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53395044%2fhow-to-troubleshoot-sql-query-within-vba-code%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here