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

  • Thread starter Thread starter Steve Castle
  • Start date Start date
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'
 
Steve

Without the SQL statement to look at, and maybe some idea of your data
structure, we could only guess what might be wrong...
 
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
 
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...
 
Back
Top