Visual Basic, SQL declaration too complex?

SQL declaration too complex?

Author	Message
Guido Coen #1 / 3	SQL declaration too complex? Hello everybody, recently I had to create a complex SQL statement within Access 2000 and wanted to use it in VB6 SP 5. Access 2000 could open a query with this statement, but after embedding exactly this SQL statement (with Copy & Paste) into my VB6 program, the program told me "TYPE MISMATCH ERROR" during execution of the statement (in DAO). I looked up all the fields in the INNER JOIN declaration, but they all are of exactly the same type. Even weirder, this happened too, after I stored the statement as a (precompiled) query definition inside the Access Database and tried to open this query with VB6 using DAO. I used this statement: sSQL = "SELECT KUG., KRT., KTI., TPLA., TPLA.Zeilenart, KRT.LfdNr, TPLA.ID, Right([TPLA].[Datum],4) AS Ausdr1, TPLA.Rate " sSQL = sSQL & "FROM TPLA INNER JOIN (KTI INNER JOIN (KUG INNER JOIN KRT ON (KUG.GruppeID = KRT.GruppeID) AND (KUG.UGruppeID = KRT.UGruppeID)) ON (KTI.GruppeID = KRT.GruppeID) AND (KTI.UGruppeID = KRT.UGruppeID) AND (KTI.LfdNr = KRT.LfdNr)) ON (TPLA.LfdNr = KRT.LfdNr) AND (TPLA.UGruppeID = KRT.UGruppeID) AND (TPLA.GruppeID = KRT.GruppeID) " sSQL = sSQL & "Where (((KUG.KUGruppeNr) >= '0016' And (KUG.KUGruppeNr) <= '0020') And ((TPLA.Zeilenart) = 2) And ((Right([TPLA].[Datum], 4)) = '2002') And ((TPLA.Rate) <> 0)) " sSQL = sSQL & "ORDER BY KUG.KUGruppeNr, KRT.Lfdnr, TPLA.ID;" Set rsKER = dbMK.OpenRecordset(sSQL, dbOpenDynaset) Well, the solution I came up with was to write my program within Access 2000 - but I would rather recode it in VB6. Any ideas? Thank you, Guido. Don't be a part of the problem - be a part of the solution!
Sat, 11 Sep 2004 07:35:11 GMT

AEE #2 / 3	SQL declaration too complex? Quote: > Hello everybody, > recently I had to create a complex SQL statement within Access 2000 > and wanted to use it in VB6 SP 5. > Access 2000 could open a query with this statement, but after > embedding exactly this SQL statement (with Copy & Paste) into my VB6 > program, the program told me "TYPE MISMATCH ERROR" during execution of > the statement (in DAO). I looked up all the fields in the INNER JOIN > declaration, but they all are of exactly the same type. > Even weirder, this happened too, after I stored the statement as a > (precompiled) query definition inside the Access Database and tried to > open this query with VB6 using DAO. > I used this statement: > sSQL = "SELECT KUG., KRT., KTI., TPLA., TPLA.Zeilenart, KRT.LfdNr, > TPLA.ID, Right([TPLA].[Datum],4) AS Ausdr1, TPLA.Rate " > sSQL = sSQL & "FROM TPLA INNER JOIN (KTI INNER JOIN (KUG INNER JOIN > KRT ON (KUG.GruppeID = KRT.GruppeID) AND (KUG.UGruppeID = > KRT.UGruppeID)) ON (KTI.GruppeID = KRT.GruppeID) AND (KTI.UGruppeID = > KRT.UGruppeID) AND (KTI.LfdNr = KRT.LfdNr)) ON (TPLA.LfdNr = > KRT.LfdNr) AND (TPLA.UGruppeID = KRT.UGruppeID) AND (TPLA.GruppeID = > KRT.GruppeID) " > sSQL = sSQL & "Where (((KUG.KUGruppeNr) >= '0016' And (KUG.KUGruppeNr) Is 'KUG.KUGruppeNr' a string? Quote: > <= '0020') And ((TPLA.Zeilenart) = 2) And ((Right([TPLA].[Datum], 4)) I haven't tested this but maybe you should use Year([TPLA].[Datum]) >= 2002 for the date check - Hide quoted text - - Show quoted text - Quote: > = '2002') And ((TPLA.Rate) <> 0)) " > sSQL = sSQL & "ORDER BY KUG.KUGruppeNr, KRT.Lfdnr, TPLA.ID;" > Set rsKER = dbMK.OpenRecordset(sSQL, dbOpenDynaset) > Well, the solution I came up with was to write my program within > Access 2000 - but I would rather recode it in VB6. > Any ideas? > Thank you, > Guido. > Don't be a part of the problem - be a part of the solution!
Sat, 11 Sep 2004 16:28:19 GMT

David Wimbu #3 / 3	SQL declaration too complex? It's the single quotes that are causing the problem. If you double them up it should work fine. Before this line: Set rsKER = dbMK.OpenRecordset(sSQL, dbOpenDynaset) insert: sSQL = Replace(sSQL, "'", "''")
Sat, 11 Sep 2004 18:48:36 GMT

Page 1 of 1

[ 3 post ]