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

Similar Threads

Left join query in ADO gives FALSE instead of NULL from linked sqlserver 0
VBA works in orignal but fails in copy of database 1
Populate Combo Boxes using Form_Current Options 3
Question On Join only if Left table Items Is Not Null 4
Copy from recordset errors in ADO 8
Populating a Combo or ListBox in Access 2000 using non-linked SQL Server tables 4
troubles not bringing in the first row of a ado query to another spreadsheet... 2
Copy Data from Excel 2007 XLSM workbook to Excel 2003 XLS workbook using ADO 2

Top