Runtime error 3319, Syntax error in Union query

[M.I.Sahil]

Can anybody let me know, why i receive a (Runtime error 3319: Syntax Error in union Query), when I run the following union Query :

sSQL = "SELECT 0 AS VoucherNo, #" & Format((txtFromDt), "dd/MMM/yy") & "# AS VoucherDate, " _
    & "Vendors.VendorName, ""[Balance Brought Forward]"" AS Narration, 0 AS Debit, 0 AS Credit, " _
    & "Sum([DR]-[CR]) AS Balance FROM Vouchers AS A INNER JOIN Vendors ON A.ClientID = Vendors.VendorID " _
    & "Where (((A.VoucherDate) < #" & Format((txtFromDt), "dd/MMM/yy") & "#)) And ((A.ClientID) = " _
    & """" & cmbLocName & """ ) GROUP BY """ & cmbLocName & """, Vendors.VendorName, " _
    & """[Balance Brought Forward]"", 0, #" & Format((txtFromDt), "dd/MMM/yy") & "# " _
    & "UNION SELECT A.VoucherNo, A.VoucherDate, Vendors.VendorName, A.Narration, A.DR AS Debit, " _
    & "A.CR AS Credit, (Select Sum([DR]-[CR]) FROM Vouchers Where Vouchers.ClientID=A.ClientID " _
    & "and VoucherNo<= A.VoucherNo ) AS Balance FROM Vouchers AS A INNER JOIN Vendors ON A.ClientID = " _
    & "Vendors.VendorID WHERE (((A.VoucherDate) Between #" & Format((txtFromDt), "dd/MMM/yy") & "# " _
    & "And #" & Format((txtToDt), "dd/MMM/yy") & "#)) AND ((A.ClientID)=""" & cmbLocName & """ )) " _
    & "GROUP BY A.VoucherNo, A.VoucherDate, A.ClientID, Vendors.VendorName, A.Narration, A.DR, A.CR"

[djjeavons]

Hi

At first glance I would say that you are missing single quote characters around your string criteria values. So instead of """" & cmbLocName & """" it would be "'" & cmbLocName & "'".

However, having said that, it would be easier if you take the value of sSQL and paste this into your database and run it, you will then get a better error description and once fixed within your database you can then fix up your SQL string.

For example, set a breakpoint on your sSQL statement, step over it and then in the immediate window type ?sSQL which will then output the value of the string. Then assuming you were using Access, open the database and create a new query and paste the string into the SQL view of the query and run it. Access will then give you an error and will probably point to the offending piece.

This is generally the best way of working out where something is going wrong within a complex query.

HTH

[M.I.Sahil]

AOA,
thnx djjeavons 4 ur reply, I tried the same with single quote but it turns into Remarks , however, when I tried in the immediate window with same code as i posted first it shows correct criteria values. But still error is remain there and same.

[djjeavons]

Hi

Did you try copying the statement from the immediate window and running it directly in your database?

[M.I.Sahil]

Dear djjeavons, Thank u very much for ur help, the query is now working properly.