Characters found at end of sql error-Repost

A

acss

My last post did not contain the sql but bottomline the i have isolated the
problem in the where section of the sql yet i cant determine what is wrong

SELECT Inv.OrderDate, Customers.CompanyName, Inv.InvoiceNum, [Service
Details].OrderID, Inv.OrderDate, Inv.InvDate, Inv.InvoiceNum,
Customers.FROMCC, Customers.BVRACCT, Customers.RECLASSTOACCT,
Customers.RECLASSTOCC, Customers.Country, [Service Details].Cost,
Customers.TYPE, 1 As SortOrder
FROM Service INNER JOIN ((Customers INNER JOIN Inv ON Customers.CustomerID =
Inv.CustomerID) INNER JOIN [Service Details] ON Inv.InvID = [Service
Details].OrderID) ON Service.SERVICEID = [Service Details].ServiceID
GROUP BY Inv.OrderDate, Customers.CompanyName, Inv.InvoiceNum, [Service
Details].OrderID, Inv.OrderDate, Inv.InvDate, Inv.InvoiceNum,
Customers.FROMCC, Customers.BVRACCT, Customers.RECLASSTOACCT,
Customers.RECLASSTOCC, Customers.Country, [Service Details].Cost,
Customers.TYPE;

WHERE (((Inv.OrderDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((Customers.Country)
Like
[Forms]![GSPExport2]![cboCity] & "*") AND
((Customers.TYPE) Like "Audits" & "*"))
UNION ALL SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.Cost) AS SF3, Null, 2 AS SortOrder
FROM Customers AS T INNER JOIN CompanyName AS TT ON T.CustomerID =
TT.CustomerID
WHERE (((TT.OrderDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((T.Customers.Country) Like
[Forms]![GSPExport2]![cboCity] & "*")) AND
((TT.TYPE) Like "Audits" & "*")
ORDER BY SortOrder, Inv.OrderDate;
Can someone see what is wrong or offer suggestions ?

thanks in advance
 
A

anlu

Hi,

I see two immediate issues with your sql:
First of all, there is a semicolon in your statement - I think Access
interprets this as an end of sql marker.
Secondly, a GROUP BY clause should come after the WHERE clause.

Hope this helps...

Regards,
anlu
 
J

John Spencer

As John Vinson noted it is a semi-colon that is causing your problem. You have
a semi-colon after Customes.Type in the GROUP BY clause of the first query.

You have other syntax errors:

== WHERE clause should be BEFORE GROUP BY or it should be a HAVING clause
== I believe you need a GROUP BY clause in the second query.

SELECT Inv.OrderDate, Customers.CompanyName
, Inv.InvoiceNum, [Service Details].OrderID, Inv.OrderDate
, Inv.InvDate, Inv.InvoiceNum,
Customers.FROMCC, Customers.BVRACCT, Customers.RECLASSTOACCT,
Customers.RECLASSTOCC, Customers.Country, [Service Details].Cost,
Customers.TYPE, 1 As SortOrder
FROM Service INNER JOIN ((Customers INNER JOIN Inv ON Customers.CustomerID =
Inv.CustomerID) INNER JOIN [Service Details] ON Inv.InvID = [Service
Details].OrderID) ON Service.SERVICEID = [Service Details].ServiceID

WHERE Inv.OrderDate Between [Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate] AND
Customers.Country Like [Forms]![GSPExport2]![cboCity] & "*" AND
Customers.TYPE Like "Audits" & "*"

GROUP BY Inv.OrderDate, Customers.CompanyName, Inv.InvoiceNum, [Service
Details].OrderID, Inv.OrderDate, Inv.InvDate, Inv.InvoiceNum,
Customers.FROMCC, Customers.BVRACCT, Customers.RECLASSTOACCT,
Customers.RECLASSTOCC, Customers.Country, [Service Details].Cost
, Customers.TYPE, 1

UNION ALL SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.Cost) AS SF3, Null, 2 AS SortOrder
FROM Customers AS T INNER JOIN CompanyName AS TT ON T.CustomerID =
TT.CustomerID

WHERE (((TT.OrderDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((T.Customers.Country) Like
[Forms]![GSPExport2]![cboCity] & "*")) AND
((TT.TYPE) Like "Audits" & "*")

GROUP BY Null, Null, Null, Null, Null, Null, Null, Null, Null, 2
ORDER BY SortOrder, Inv.OrderDate;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
My last post did not contain the sql but bottomline the i have isolated the
problem in the where section of the sql yet i cant determine what is wrong

SELECT Inv.OrderDate, Customers.CompanyName, Inv.InvoiceNum, [Service
Details].OrderID, Inv.OrderDate, Inv.InvDate, Inv.InvoiceNum,
Customers.FROMCC, Customers.BVRACCT, Customers.RECLASSTOACCT,
Customers.RECLASSTOCC, Customers.Country, [Service Details].Cost,
Customers.TYPE, 1 As SortOrder
FROM Service INNER JOIN ((Customers INNER JOIN Inv ON Customers.CustomerID =
Inv.CustomerID) INNER JOIN [Service Details] ON Inv.InvID = [Service
Details].OrderID) ON Service.SERVICEID = [Service Details].ServiceID
GROUP BY Inv.OrderDate, Customers.CompanyName, Inv.InvoiceNum, [Service
Details].OrderID, Inv.OrderDate, Inv.InvDate, Inv.InvoiceNum,
Customers.FROMCC, Customers.BVRACCT, Customers.RECLASSTOACCT,
Customers.RECLASSTOCC, Customers.Country, [Service Details].Cost,
Customers.TYPE;

WHERE (((Inv.OrderDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((Customers.Country)
Like
[Forms]![GSPExport2]![cboCity] & "*") AND
((Customers.TYPE) Like "Audits" & "*"))
UNION ALL SELECT Null, Null, Null, Null, Null, Null, Null, Null,
Sum(TT.Cost) AS SF3, Null, 2 AS SortOrder
FROM Customers AS T INNER JOIN CompanyName AS TT ON T.CustomerID =
TT.CustomerID
WHERE (((TT.OrderDate) Between
[Forms]![GSPExport2]![txtStartDate] And
[Forms]![GSPExport2]![txtEndDate]) AND ((T.Customers.Country) Like
[Forms]![GSPExport2]![cboCity] & "*")) AND
((TT.TYPE) Like "Audits" & "*")
ORDER BY SortOrder, Inv.OrderDate;
Can someone see what is wrong or offer suggestions ?

thanks in advance
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query Edit 1

Top