SQL statement for multiple tbls: Inner joins/VB code.....Please help!

[jbeale]

I am pulling information from 3 tables to place in a recordset then print to a text file. When I scale back and only request info from 1 table, I am successful. However, when I try to pull from the 3 tables that I need, I am unsuccessful.
Here is the way the tables relate:
T20 T03 T20 T06
------------------------ -------------------
CDATTIM --> CDATTIM USERID --> USERID
RECCD --> RECCD
CRNODE --> CRNODE
CDATE --> CDATE
CTIME --> CTIME

HERE IS THE SQL STATEMENT I BUILD:
strSQL = "SELECT T20.ESTATCD, " & _
"T20.EQCD, " & _
"T20.USERID, " & _
"T20.CDATE, " & _
"T06.LNAME, " & _
"T06.FNAME, " & _
"T03.POL " & _
"FROM USERID.T20 T20 " & _
"INNER JOIN USERID.T06 T06 " & _
"ON (T20.USERID = T06.USERID) " & _
"INNER JOIN USERID.T03 T03 " & _
"ON (T20.CDATTIM = T03.CDATTIM AND " & _
"T20.RECCD = T03.RECCD AND " & _
"T20.CRNODE = T03.CRNODE AND " & _
"T20.CDATE = T03.CDATE AND " & _
"T20.CTIME = T03.CTIME) " & _
"WHERE T20.USERID = 'ANDREWS' AND " & _
"(T20.ESTATCD IN 'UWI', 'UWC', 'DEC') " & _
"ORDER BY T03.POL ASC "

The statement above does not get any SQL syntax errors, it goes to the database, but when it comes back and I check the status of the recordset, it is 0 (closed). When I go back to pulling from 1 table, the recordset status is 1.

Does anyone have any ideas what I am doing wrong. I have gone through books and asked many co-workers without any luck. I also tried creating a Crystal Report using Select Expert...that worked. I also tried linking the tables in an Access Database and trying to do a query...that worked on 1 table, but not multiple.
Thanks in advance for any help.
JBEALE

 

[tlbroadbent]

Obvious question - Is there a matching record on BOTH the T03 AND T06 tables for any record on the T20 table?

Less obvious - Do all the column data types match - especially the date and time columns?

Which database are you connecting to? How are you connecting - ODBC, ADO or other? Do you have another query tool you can use to test the query? Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[jbeale]

Here are the answers as best as I can do for now...I thought (maybe ignorantly) that since everything worked when setting up the select expert in Crystal Reports (the SQL it generated looked very simple), I wouldn't have too much trouble setting up the SQL in VB.

Answer to obvious question: I'm not sure...T06 is just a list of Userids and personal information. T03 contains information on each transaction a person does, but does not contain the userid. T20 is a history table creating a record each time a record in T03 is created.

Less obvious: I need to check on this one.

I am connecting to DB2 via ODBC:
Dim strCN As String
strCN = "ODBC;database=DB;uid=myid;pwd=mypw;dsn=DB;"
Set cnADO = New Connection
cnADO.ConnectionTimeout = 60
cnADO.CursorLocation = adUseServer
cnADO.Open strCN

Once I estable the SQL string, I open the recordset via:
Dim rsADO As New Recordset
rsADO.Open strSQL, cnADO, adOpenDynamic, adLockReadOnly

Does this help?

 

[tlbroadbent]

After reading your response, I looked again at the query and saw a small syntax problem. Sorry I din't catch it before. The ( is misplaced in the IN clause.

Current syntax in your code:
"WHERE T20.USERID = 'ANDREWS' AND " & _
"(T20.ESTATCD IN 'UWI', 'UWC', 'DEC') " & _

Should be:
"WHERE T20.USERID = 'ANDREWS' AND " & _
"T20.ESTATCD IN ('UWI', 'UWC', 'DEC') " & _ Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin