I mentioned that at the end of my original message. I've tried it both ways and it doesn't solve the problem. The query is good. For some reason though Access or MySQL is removing the IF statements in the middle of it. What next?
>>> "Freddie Sorensen" <[EMAIL PROTECTED]> 2/26/04 12:25:03 PM >>> 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]
