SQL syntax issue with ADODB Recordset on a calc Alias

[HandymanHerb]

Using Access 2010 with BE - MySql (ODBC Driver 3.51) No errors when I leave out the alias and its reference. This statement was first created using Access Query Builder then moved to VBA and used in a DAO Recordset (yes this program has 2 backends) this worked just fine. The alias takes several fields to make up a key that will match one of the table records that I need to separate. Output is a string 18 char long. First 4 char uses an integer field, changes it to text, measures the length (1 to 3 char) and adds leading zero's to make it 4 char long. Then it adds 12 spaces and a 2 char field.

SELECT INV_MAST.PRODUCT_CATEGORY, INV_STORE.STORE_NUMBER, INV_STORE.CKEY, String(4-Len(Format([INV_MAST].[PRODUCT_CATEGORY])),'0') & Format([INV_MAST].[PRODUCT_CATEGORY]) & ' ' & [INV_STORE].[STORE_NUMBER] AS HKEY
FROM INV_MAST INNER JOIN INV_STORE ON INV_MAST.ITEM_NUMBER = INV_STORE.ITEM_NUMBER
WHERE (((String(4-Len(Format([INV_MAST].[PRODUCT_CATEGORY])),'0') & Format([INV_MAST].[PRODUCT_CATEGORY]) & ' ' & [INV_STORE].[STORE_NUMBER])=[INV_STORE].[CKEY]));

Gives me this error runtime error -2147217900 (80040e14) [mysql][odbc 3.51 driver][mysqld-4.0.18-log]you have an error in your sql syntax

[jforbes]

When executing SQL with ODBC, you need to provide the native SQL. I don't think there is a String Function in MySQL. I'm guessing then that the String, Len, Format Functions are to be called while making the String, from VBA. If this is the SQL that you are sending to the Database, you'll either need to find SQL replacements for the Functions mentioned or Build your String with those functions outside of Quotes, before performing the Execute or OpenRecordset.

[HandymanHerb]

I was afraid of that. Any suggestions as to how best do this since I have to read the records first before I can come up with this temporary key. There are only 10 fields within the record that I need but there could be 100,000+ records. I dont think loading it into an array is the answer. I know there are plenty of ways to do this so what I am asking is how would you do this?

[jforbes]

It depends on what needs to be done with the data.

If you are selecting it into a RecordSet all you would need to do is to Select the parts of the HKEY then use the String(), Len(), Format() functions in VBA to build the HKEY.

If the Select statement is only a part of the complete SQL, like an insert statement, then it is best to convert the formatting to some native SQL and run it inline so that it can be done in one single operation.

Hopefully that gets you going. If you need additional help, you can post more of the code so we can see how it is being used and we can give a more concise recommendation.

[HandymanHerb]

Thank you for your knowledge. Easiest method is to find the MySQL syntax that would allow me to do the same thing or build a dump table in Access that I can build the HKEY then use it to sort records. Again Thank You.