Error using CAST expression in Access 2000

[bessebo]

I can enter the SQL line below in Transact-SQL in SQL 7.0 and it runs fine. When I try to run it in Access 2000 I get the following error:

Syntax Error (Missing Operator) in query expression
'CAST(prod_list.prod_no AS char(10)) = ord_list.part_no)'

Based on all documentation I've read it looks fine and it runs in Query Analyzer in SQL 7.

All I'm doing with the CAST expression is converting an INT field to a char(10) field to compare the prod_no field to the part_no field.

Here's the SQL line:

SELECT prod_no, prod_list.seq_no, prod_list.part_no, prod_list.description, prod_list.plan_qty
FROM (OrdItem INNER JOIN OrdMain ON OrdItem.Order_ID = OrdMain.Order_ID) INNER JOIN (prod_list INNER JOIN ord_list ON CAST(prod_list.prod_no AS char(10)) = ord_list.part_no) ON (OrdItem.SequenceOfItems = ord_list.line_no) AND (OrdMain.ExternalID = ord_list.order_no);


Any help would be appreciated...

Thanks,
Bessebo

 

[RickSpr]

The SQL statement must obey the syntax rules of the engine which interprets it. When you run a local query, it's interpreted by Jet, which uses VBA to help interpret the expressions. Neither Jet nor VBA has a CAST function. That's why you're getting the error.

You may be able to run this as a pass-through query to SQL Server, in which case the Transact-SQL engine would interpret it correctly (I'm guessing--I've never used a pass-through query, but this is how I understand it should work). It would also run faster, since the work is all being done on the system where the data is located.

Alternatively, you could convert your CAST expression into the corresponding VBA expression: Format$("0000000000", prod_list.prod_no). Rick Sprague