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

B

Brian

(previously posted in .formscoding group but got no responses)

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 in ADO gives FALSE instead of NULL from linked sqlserver 0
Copy from recordset errors in ADO 8
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
query runs in interface but not over ado, can't be stored via adox 0
OLEDB to pull alist from AS2005 0
EXCEL-VBA, ADO not connecting to a TXT file 1
Using ADO Recordsets to pull data from a closed Excel WB. 5

Top