Recordset returns the correct number of row but with all field empty

Question

hi I have the same copy of access running in 3 cities right now. they work prefectly ok. they are 99% the same with one minor difference. Each of them has two views which use different odbc connection to different cities DB (all these db are SQL 2005). The views act as datasource for some two very simple queries. however, while i tried to make a new copy for a new city, i found that one of the simple internal qeury returns the correct number of row but all data are empty while the other query functions correctly.

i check the data of these two views, the data is correct.

The one causing problem are like Select * from View_Top where Name = "ABC" when the recordset returns, even rs!Name give me an empty string.

Please help

Answer 1

Well the query looks a little wrong to me, try using ' instead of " to delimit your ABC string...

Answer 2

Without the definition of VIEW_TOP it's hard to tell where your error is, but if you're getting rows but the columns are NULL I'm guessing that VIEW_TOP (or something it depends on) includes an OUTER JOIN and you're pulling the columns from the wrong side of the JOIN.

Answer 3

view_top definition "Name, Account, AccountCode, Amount, Date...." Select Statements "Select Name, Account, AccountCode, Amount, Date From View_Top Where Name = 'xxx'"

I found that if i omit the Amount, everything works.

Though i still don't understand why.

Answer 4

SELECT acc.FIRM, acc.OFFICE, acc.ACCOUNT, a.CONV_CODE, a.OTHER_AMT AS AMOUNT, a.TRANS_DATE, a.DESCRIPTN, a.JRNAL_TYPE FROM AccTrans AS a LEFT OUTER JOIN ACC AS acc ON a.ACCNT_CODE = acc.ACCNT_CODE WHERE
(acc.SUN_DB = 'IF1') AND (ANAL_T0 <> '') AND ( a.TRANS_DATE < '20091022' ) AND (a.JRNAL_TYPE = 'MATCH');

This is the definition of the view. Indeed, in Access i am able to view the result of this query, it has data. that's why i know the recordset returns the correct number of row (by counting the loop in the code). sorry for my mistakes, i use Account in the where clause, the select statements should be like

select Firm, Office, Account, Trans_Date.... from view_top where account = 'ABC'

the query returns the right number of row but all row data (even the account field) are empty string.

then i found out what really cause the problem is the AMOUNT field, if i omit the amount, everything works. though i don't understand why.