Hi all. I'm having a problem with a pass-through query in Access (2002). I'm using MySQL-4.0.3 and MyODBC 3.51.04.
I have already posted to the MyODBC list, but there doesn't appear to be any traffic, and I'm not sure if it's an ODBC problem or not, so I'm reposting here... Table definition and some test data: CREATE TABLE Leads ( DanPK mediumint(8) unsigned NOT NULL auto_increment, MyStamp timestamp(14) NOT NULL, LeadNo mediumint(9) unsigned NOT NULL default '0', IssueDate date NOT NULL default '0000-00-00', IssuedTo tinyint(3) unsigned NOT NULL default '0', CompleteDate date default '0000-00-00', PRIMARY KEY (DanPK), KEY IDX_LeadNo (LeadNo) ) TYPE=MyISAM; INSERT INTO Leads VALUES (5,20020828101838,4,'2001-12-13',67,'2002-08-28'),(101,20020605104708,100,'2001-12-18',67,'2001-02-18'); Access identifies my primary key (DanPK) as a long integer. If I run the following query as a standard Access query, it runs correctly: SELECT LeadNo, MAX(DanPK) AS MaxOfDanPK FROM Leads GROUP BY LeadNo; I can then use this query inside another (Access) query which works. But it's very slow due to the size of the tables. If I turn the above query into a pass-through query and set up the ODBC connection string it runs MUCH faster, but unfortunately the MAX(DanPK) AS MaxOfDanPK function returns a string instead of the expected integer data type. In Access, this can be seen when you open the query and all the values are left-aligned (which denotes a text / string field) instead of being right-aligned (which denotes a numeric field). Further, if I use this pass-though query inside another query and try to join on the offending field (back to the same field - the primay key in the Leads table) I get 'Type Mismatch In Expression' error from Access which usually means that 2 fields in a join statement are of different data types. I have tried using: CAST(MAX(DanPK) AS UNSIGNED) and also CAST(MAX(DanPK) AS UNSIGNED INTEGER) and also the CONVERT equivalents. These also return a string data type. Anyone have any ideas why I'm getting a string instead of an integer? Thanks in advance! Dan -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
