Visual Basic&database, Why does this INNER JOIN not work?

Why does this INNER JOIN not work?

Author	Message
Richard Stratto #1 / 3	Why does this INNER JOIN not work? Can anyone tell me why this inner join does not work? I am converting a program from DAO to ADO and although the inner join worked when I was using DAO on an ACCESS.mdb database, it does not work on a SQL7 database. Set rsList1List = New Recordset rsList1List.Open "Select A1.WONumber, A1.WORefNumber From WONumber A1 INNER JOIN ShipBatch A2 ON A1.WORefNumber = A2.WORefNumber Where NOT IsDate(A2.WOShippedDate) AND A1.WONumber Like '" & WO & "' Order By A1.WORefNumber", SQLdb, adOpenStatic, adLockOptimistic, adCmdText Thanks for any help you can offer. Richard
Sun, 22 Jun 2003 05:15:07 GMT

Gary Labowit #2 / 3	Why does this INNER JOIN not work? Here are differences between DAO and ADO taken from http:// msdn.microsoft.com / library / officedev / off2000 / acconComparingAccessSQLSSQLSyntax.htm Wildcards DAO:Asterisk () matches zero or more characters. ADO:Percent sign (%) matches zero or more characters. DAO:Question mark (?) matches a single character. ADO:Underscore (_) matches a single character. DAO:Exclamation point (!) means not in list ADO:Up caret (^) means not in list. DAO:Pound sign (#) means single digit. ADO:No equivalent to the pound sign (#). Gary Quote: > Can anyone tell me why this inner join does not work? I am converting a > program from DAO to ADO and although the inner join worked when I was using > DAO on an ACCESS.mdb database, it does not work on a SQL7 database. > Set rsList1List = New Recordset > rsList1List.Open "Select A1.WONumber, A1.WORefNumber From WONumber A1 INNER > JOIN ShipBatch A2 ON A1.WORefNumber = A2.WORefNumber Where NOT > IsDate(A2.WOShippedDate) AND A1.WONumber Like '" & WO & "*' Order By > A1.WORefNumber", SQLdb, adOpenStatic, adLockOptimistic, adCmdText > Thanks for any help you can offer. > Richard
Tue, 24 Jun 2003 02:25:54 GMT

Richard Stratto #3 / 3	Why does this INNER JOIN not work? Thanks Gary. That helped. I also found out that I had to change the NOT IsDate() logic to this. AND IsDate(A2.WOShippedDate) <> 1 Quote: >Here are differences between DAO and ADO taken from >http:// msdn.microsoft.com / library / officedev / off2000 / >acconComparingAccessSQLSSQLSyntax.htm >Wildcards >DAO:Asterisk () matches zero or more characters. >ADO:Percent sign (%) matches zero or more characters. >DAO:Question mark (?) matches a single character. >ADO:Underscore (_) matches a single character. >DAO:Exclamation point (!) means not in list >ADO:Up caret (^) means not in list. >DAO:Pound sign (#) means single digit. >ADO:No equivalent to the pound sign (#). >Gary >> Can anyone tell me why this inner join does not work? I am converting a >> program from DAO to ADO and although the inner join worked when I was >using >> DAO on an ACCESS.mdb database, it does not work on a SQL7 database. >> Set rsList1List = New Recordset >> rsList1List.Open "Select A1.WONumber, A1.WORefNumber From WONumber A1 >INNER >> JOIN ShipBatch A2 ON A1.WORefNumber = A2.WORefNumber Where NOT >> IsDate(A2.WOShippedDate) AND A1.WONumber Like '" & WO & "*' Order By >> A1.WORefNumber", SQLdb, adOpenStatic, adLockOptimistic, adCmdText >> Thanks for any help you can offer. >> Richard
Tue, 24 Jun 2003 21:18:41 GMT

Page 1 of 1

[ 3 post ]