Access doesn't like its own SQL Allen_N 19.09.2006 23:42:01 Here's a weird one. The code Set qy = CurrentDb.QueryDefs(strSourceQuery) strSQL = qy.SQL Set qy = CurrentDb.QueryDefs("Kits Found Special") qy.SQL = strSQL produces error 3131: "Syntax error in FROM clause", despite the fact that the 1st query works fine if run from the database window. After the 2nd line, strSQL is "SELECT [Sub Kit Comps].KitItem FROM [SELECT [Sub Kit Comps].KitItem, [Sub Kit Comps].ITEM, [Sub Kit Comps].PER, [Sub Kit Comps_1].ITEM, [Sub Kit Comps_1].PER FROM [Sub Kit Comps] INNER JOIN [Sub Kit Comps] AS [Sub Kit Comps_1] ON [Sub Kit Comps].KitItem = [Sub Kit Comps_1].KitItem WHERE ((([Sub Kit Comps].ITEM)='600-211-1231') AND (([Sub Kit Comps].PER)=4) AND (([Sub Kit Comps_1].ITEM)='600-212-1511') AND (([Sub Kit Comps_1].PER)=2))]. AS [%$##[ at ]_Alias] WHERE [Sub Kit Comps].KitItem IN (SELECT KitItem AS k2 FROM [Sub Kit Comps] AS [Sub Kit Comps_2] GROUP BY KitItem HAVING COUNT (1) < 7);" (note that Access added the "]. AS [%$##[ at ]_Alias]" foolishness, not I). If I look at this string by hovering the cursor over it while execution is paused, it shows 2 rectangular symbols before the 1st "FROM". Thinking that the code was getting a CR-LF that it didn't like, I killed the EOL's in the original query (from within the database window), but this did not prevent the error. Has anyone seen this before? Is it something to do with the length of the string strSQL? Thanks!

Re: Access doesn't like its own SQL Stefan Hoffmann <stefan.hoffmann[ at ]explido.de> 20.09.2006 09:36:29 hi Allen, Allen_N wrote: [Quoted Text] > Here's a weird one. The code > > Set qy = CurrentDb.QueryDefs(strSourceQuery) > strSQL = qy.SQL > > Set qy = CurrentDb.QueryDefs("Kits Found Special") > qy.SQL = strSQL > > produces error 3131: "Syntax error in FROM clause", despite the fact that > the 1st query works fine if run from the database window. > (note that Access added the "]. AS [%$##[ at ]_Alias]" foolishness, not I). This alias is need by Jet to run the query. Access use this not so nice default alias, which is not reusable as you have noticed. The simple solution is to use a extra query for your subselect. btw, under Access 2003 this probem is fixed. mfG --> stefan <--