I'm getting message 'Syntax error in WHERE clause'

S

Steve Castle

I have a report with approx 45 fields of data, which gives
all order details of a particular order. This works fine
but when i want to add an extra field via the RecordSource
in the Properties window of the report (SQL
Statement:Query Builder) I get the following error message
'Syntax error in the WHERE clause'

Can anybody help with this becasue i desperately need to
get this functioning correctly

I also sometimes get the following message
'Syntax error in the FROM clause'
 
J

Jeff Boyce

Steve

Without the SQL statement to look at, and maybe some idea of your data
structure, we could only guess what might be wrong...
 
G

Guest

The SQL code is as follows:

SELECT [Orders].[SupplierID], [Suppliers].[Address] AS
Suppliers_Address, [Suppliers].[Address2], [Suppliers].
[City] AS Suppliers_City, [Suppliers].[Region] AS
Suppliers_Region, [Suppliers].[PostalCode] AS
Suppliers_PostalCode, [Suppliers].[Country] AS
Suppliers_Country, [Orders].[CustomerID], [Customers].
[Address] AS Customers_Address, [Customers].[Address1],
[Customers].[City] AS Customers_City, [Customers].[Region]
AS Customers_Region, [Customers].[PostalCode] AS
Customers_PostalCode, [Customers].[Country] AS
Customers_Country, [Orders].[DeliveryName], [Orders].
[DeliveryAddress], [Orders].[DeliveryAddress2], [Orders].
[DeliveryCity], [Orders].[DeliveryRegion], [Orders].
[DeliveryPostalCode], [Orders].[DeliveryCountry], [Orders].
[TransporterID], [Transporters].[TelNo], [Transporters].
[FaxNo], [Orders].[TransporterRate], [Orders].[OrderID],
[Orders].[SalesID], [Orders].[PSNo], [Orders].[Notes],
[Orders].[OrderDate], [Orders].[RequiredDate], [Order
Details].[ProductID], [Order Details].[UnitPrice], [Order
Details].[ProductCode], [Order Details].[NoCases], [Order
Details].[WeightEach], [Order Details].[Quantity], [Order
Details].[CostPrice], [Order Details].[Stock Ref], [Order
Details].[SaleCurrency], [Order Details].[CostCurrency],
[Orders].[Agent], [Orders].[CollectionDate], [Suppliers].
[E-Mail], [Suppliers].[ContactName], [Customers].[E-Mail],
[Customers].[ContactName], [Order Details].[NoPallets],
[Orders].[ReleaseNo], [Orders].[CollName], [Orders].
[CollAddress], [Orders].[CollAddress2] FROM Transporters
INNER JOIN (Suppliers INNER JOIN ((Customers INNER JOIN
Orders ON [Customers].[CustomerID]=[Orders].[CustomerID])
INNER JOIN [Order Details] ON [Orders].[OrderID]=[Order
Details].[OrderID]) ON [Suppliers].[SupplierID]=[Orders].
[SupplierID]) ON [Transporters].[TransporterID]=[Orders].
[TransporterID] WHERE
 
J

John Spencer (MVP)

If that is the entire SQL, then you have nothing after the WHERE.

There should be something like

WHERE Orders.SupplierID = 2878

Since you don't show any criteria, the WHERE should not be there at all.

The SQL code is as follows:

SELECT [Orders].[SupplierID], [Suppliers].[Address] AS
Suppliers_Address, [Suppliers].[Address2], [Suppliers].
[City] AS Suppliers_City, [Suppliers].[Region] AS
Suppliers_Region, [Suppliers].[PostalCode] AS
Suppliers_PostalCode, [Suppliers].[Country] AS
Suppliers_Country, [Orders].[CustomerID], [Customers].
[Address] AS Customers_Address, [Customers].[Address1],
[Customers].[City] AS Customers_City, [Customers].[Region]
AS Customers_Region, [Customers].[PostalCode] AS
Customers_PostalCode, [Customers].[Country] AS
Customers_Country, [Orders].[DeliveryName], [Orders].
[DeliveryAddress], [Orders].[DeliveryAddress2], [Orders].
[DeliveryCity], [Orders].[DeliveryRegion], [Orders].
[DeliveryPostalCode], [Orders].[DeliveryCountry], [Orders].
[TransporterID], [Transporters].[TelNo], [Transporters].
[FaxNo], [Orders].[TransporterRate], [Orders].[OrderID],
[Orders].[SalesID], [Orders].[PSNo], [Orders].[Notes],
[Orders].[OrderDate], [Orders].[RequiredDate], [Order
Details].[ProductID], [Order Details].[UnitPrice], [Order
Details].[ProductCode], [Order Details].[NoCases], [Order
Details].[WeightEach], [Order Details].[Quantity], [Order
Details].[CostPrice], [Order Details].[Stock Ref], [Order
Details].[SaleCurrency], [Order Details].[CostCurrency],
[Orders].[Agent], [Orders].[CollectionDate], [Suppliers].
[E-Mail], [Suppliers].[ContactName], [Customers].[E-Mail],
[Customers].[ContactName], [Order Details].[NoPallets],
[Orders].[ReleaseNo], [Orders].[CollName], [Orders].
[CollAddress], [Orders].[CollAddress2] FROM Transporters
INNER JOIN (Suppliers INNER JOIN ((Customers INNER JOIN
Orders ON [Customers].[CustomerID]=[Orders].[CustomerID])
INNER JOIN [Order Details] ON [Orders].[OrderID]=[Order
Details].[OrderID]) ON [Suppliers].[SupplierID]=[Orders].
[SupplierID]) ON [Transporters].[TransporterID]=[Orders].
[TransporterID] WHERE
-----Original Message-----
Steve

Without the SQL statement to look at, and maybe some idea of your data
structure, we could only guess what might be wrong...

--
More info, please ...

Jeff Boyce
<Access MVP>

.
Click to expand...
Click to expand...
 
Top