Hello Everyone, If I run the following query in MySQL Control Center or MySQL-Front it works correctly, SELECT -1 AS ProductID, "Add New Part" AS PartNumber, "" AS VendorPartNo, "" AS Description, "" AS VendorStatus FROM Products UNION SELECT ProductID, PartNumber, If(SubNo=1135, VendorPart,AltVendorPart) AS VendorPartNo, Description, If(SubNo=1135, "Primary","Alternate") AS VendorStatus FROM Products WHERE ((Obsolete=0) AND (SubNo=1135)) OR ((AltSubNo=1135)) ORDER BY ProductID, VendorPartNo, VendorStatus DESC;
If I run the same query in MSAccess, where my user interface is, I get the following error, [MySQL][ODBC 3.51 Driver][mysqld-4.1.1-alpha-log]You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server version for the syntax to use near 'Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = (#1064) My log file shows the following, 1163 Query (SELECT ProductID ,NSIPartNumber ,,Description FROM products WHERE (((Obsolete = 0 ) AND (SubNo = 1135 ) ) OR (AltSubNo = 1135 ) ) ) UNION (SELECT -1 ,'Add New Part' ,'' ,'' FROM products ) I'm aware of the difference between Access and MySQL regarding the IIF versus IF and I've tried the query both ways with no success. SubNo is a valid ID. In both MySQL Control Center or MySQL-Front this query returns 58 records in about on third of a second. Any thoughts?
