inner join query in access not working- can anyone look into this pls

[chaitanya02]

Hi,,,

I have 2 tables in access: tblUrl (software Url, contract number- 2 columns)
and tblAccContract(4 columns-accountid, contract id, contract date, email addr)

I am querying these two tables using inner join. I get the result properly, but unable to retrieve the values from those tables. I think the problem is with the query I used for joining these two tables.

Please check with this:

The output has to be like:"Download your software here: www.google.com...."
But it says: "Download your software here..."
It doesnt display the Url in the tblUrl table


code is:


strSQL ="SELECT tblAccContract.accountId,tblAccContract.emailAddr, tblAccContract.contractNo, tblUrl.softwareUrl"
strSQL =strSQL & " FROM tblAccContract INNER JOIN tblUrl ON tblAccContract.contractNo = tblUrl.contractNo"
strSQL =strSQL & " WHERE (tblAccContract.emailAddr)='" & strEmail &"'"
objRS.open strSQL,conn
strUrl=objRS("tblUrl.softwareUrl")
response.write strUrl


Anyone familiar with this - please check with the code....I just need to get the values from multiple tables when Join is sued in Access.


Thanks
Tanya

[jhardman]

Tanya,

Welcome to the scripts DN.

I've never used "inner Join" before. you can relate the tables like this:

SELECT * FROM tblAccContract, tblUrl WHERE tblAccContract.contractNo = tblUrl.contractNo AND emailAddr='" & strEmail &"'"

That said, I don't know why your code didn't work. I would suggest trying this and putting in a simple loop to list everything in the record set to troublshoot:

for each x in objRS.fields
   response.write x & ": " & objRS(x) & "<br>" & vbNewLine
next
 

Let me know if this helps.

Jared