Nsryjdtyk

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 3319: 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 3319:

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







        '###########################################################

        '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]>=#" & DryStartDate & "# And [PreStressStackDate]<=#" & DryEndDate & "#)" & _

                    " And [CurrentLevelOfCompletion]=1073741829),1,0)) AS [PreStress Stackup], Sum(IIf(([StackCompressionDate]>=#" & DryStartDate & "#" & _

                    " And [StackCompressionDate]<=#" & DryEndDate & "#) And [CurrentLevelOfCompletion]=1073741845,1,0)) AS [Stack Compression]," & _

                    " Sum(IIf(([TestingDate]>=#" & DryStartDate & "# And [TestingDate]<=#" & DryEndDate & "#) And [CurrentLevelOfCompletion]=1073741909,1,0)) AS [Testing]," & _

                    " Sum(IIf(([ShroudAssemblyDate]>=#" & DryStartDate & "# And [ShroudAssemblyDate]<=#" & DryEndDate & "#) And ([CurrentLevelOfCompletion]=1073742165 Or" & _

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

                    " And [CurrentLevelOfCompletion]=1073743189,1,0)) AS [Transformer Installation]" & _

                    " FROM TR343Dryside" & _

                    " WHERE (([TransducerSN] Like ""CR*""));"











    Me.sfrmCraneDrySidePassFailDateRange_New.Form.RecordSource = strDrySQL_New

    Me.sfrmCraneDrySidePassFailDateRange_New.Visible = True



 End If

End Sub

The result when the query is activated is: Run-time error '3319':
Syntax error within union query.

The problem is raised within the line of:
Me.sfrmCraneDrySidePassFailDateRange_New.Form.RecordSource = strDrySQL_New

At least here, you have an extra closing parenthesis:

.. And [CurrentLevelOfCompletion]=1073741829),1,0)) 

Should be:

.. And [CurrentLevelOfCompletion]=1073741829,1,0)) 

Insert a line:

Debug.Print strDrySQL_New

and study the print.