Left join in ADO gives FALSE instead of NULL from linked sqlserver

B

Brian

Hi,
I have been using DAO and ADO for years to get at data from linked tables
and by accident I am finding a fault with ADO.

My query is below and tmpTrans is a local Access table while all the others
are SQL2005 tables. I am linked to all of them.

If I use DAO - Set rsMembers = CurrentDb.OpenRecordset(strSQL,
dbOpenDynaset, dbSeeChanges, dbReadOnly)
and get 0001 1 28921.71 False 0
0003 1 0.14 Null
Null

If I use ADO - rsMembers.Open strSQL, CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly, adCmdText
and get 0001 1 28921.71 False 0
0003 1 0.14 False
Null ' which is WRONG !!!!!!!!!! False should be Null

SELECT tblMembers.AcNo,
tmpTrans.AcTyp, tmpTrans.Balance,
tblOver70Bals.Covered, tblOver70Bals.ShareBal,
FROM (tblMembers LEFT JOIN tblOver70Bals ON tblMembers.AcNo =
tblOver70Bals.AcNo) INNER JOIN tmpTrans ON tblMembers.AcNo = tmpTrans.AcNo
WHERE (tblMembers.AcOpen <> 0)
ORDER BY tmpTrans.AcTyp, tmpTrans.AcNo;

Can anyone explain please?
Any solution? I prefer ADO.
I may now have to go back and review mounds and mounds of queries.

Thanks
Brian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top