Ed The MS Access SQL syntax for if() is iif(condition, then stuff, else stuff)
Maybe that's the problem, I am not sure - try it Freddie -----Ursprüngliche Nachricht----- Von: Ed Reed [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 26. Februar 2004 02:09 An: [EMAIL PROTECTED] Betreff: Query error in Access 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? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
