Missing Operator in query

[Piri]

I am trying to run the following - all fields concerned are text:
Dim dbs As Database
Set dbs = CurrentDb()

dbs.Execute "SELECT IN_Main.PartNo, IN_BinLocation.Description FROM " _
& "IN_BinLocation INNER JOIN IN_Main ON IN_BinLocation.IN_MainID =
'[IN_Main.InventoryID] " _
& " WHERE (((IN_BinLocation.IN_LogicalID) = 'ZZZZZZZZZZ0000000000'))
ORDER BY IN_Main.PartNo;"

where the table IN_Main holds a list of part numbers and the table
IN_BinLocation a list of locations.
IN_Main.InventoryID is the common part number identifier in both
tables. IN_LogicalID in the IN_BinLocation table identifies the
warehouse.


I am getting an error message :
Error - 3075 - Syntax error (missing operator) in query expression
'IN_BinLocation.IN_MainID = '[IN_Main.InventoryID] WHERE
(((IN_BinLocation.IN_LogicalID) = 'ZZZZZZZZZZ0000000000'))ORDER BY
IN_Main.PartNo;'.

I've tried to find the problem to no avail.
Any help appreciated.

Piri

 

[Allen Browne]

Square brackets in the ON clause are wrong (must be around the table and
field names individually), and it looks like there is a single quote there
as well.

Try this approach:

Dim strSql As String
strSql = "SELECT [IN_Main].PartNo, [IN_BinLocation].[Description] " & _
"FROM [IN_BinLocation] INNER JOIN [IN_Main] " & _
"ON [IN_BinLocation].[IN_MainID] = [IN_Main].[InventoryID] " & _
"WHERE [IN_BinLocation].[IN_LogicalID] = 'ZZZZZZZZZZ0000000000' " & _
"ORDER BY [IN_Main].PartNo;"
dbs.Execute strSql, dbFailOnError

If it still fails, you can insert:
Debug.Print strSql
and then view the output in the Immeidate Window (Ctrl+G.)

 

[Piri]

Thanks for the help Allen,
Problem solved.

Cheers,
Piri

Square brackets in the ON clause are wrong (must be around the table and
field names individually), and it looks like there is a single quote there
as well.

Try this approach:

Dim strSql As String
strSql = "SELECT [IN_Main].PartNo, [IN_BinLocation].[Description] " & _
"FROM [IN_BinLocation] INNER JOIN [IN_Main] " & _
"ON [IN_BinLocation].[IN_MainID] = [IN_Main].[InventoryID] " & _
"WHERE [IN_BinLocation].[IN_LogicalID] = 'ZZZZZZZZZZ0000000000' " & _
"ORDER BY [IN_Main].PartNo;"
dbs.Execute strSql, dbFailOnError

If it still fails, you can insert:
Debug.Print strSql
and then view the output in the Immeidate Window (Ctrl+G.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to run the following - all fields concerned are text:
Dim dbs As Database
Set dbs = CurrentDb()

dbs.Execute "SELECT IN_Main.PartNo, IN_BinLocation.Description FROM " _
& "IN_BinLocation INNER JOIN IN_Main ON IN_BinLocation.IN_MainID =
'[IN_Main.InventoryID] " _
& " WHERE (((IN_BinLocation.IN_LogicalID) = 'ZZZZZZZZZZ0000000000'))
ORDER BY IN_Main.PartNo;"

where the table IN_Main holds a list of part numbers and the table
IN_BinLocation a list of locations.
IN_Main.InventoryID is the common part number identifier in both
tables. IN_LogicalID in the IN_BinLocation table identifies the
warehouse.


I am getting an error message :
Error - 3075 - Syntax error (missing operator) in query expression
'IN_BinLocation.IN_MainID = '[IN_Main.InventoryID] WHERE
(((IN_BinLocation.IN_LogicalID) = 'ZZZZZZZZZZ0000000000'))ORDER BY
IN_Main.PartNo;'.

I've tried to find the problem to no avail.
Any help appreciated.

Piri