Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Column Using QueryTables.Add
Hi,
I am having problem with QueryTables.Add as follow .... strSQL = "Select STRING1, NUMBER1, STRING2 from myTable" Set rs = cn.Execute(strSQL) .... Set qTable = ActiveSheet.QueryTables.Add( rs, RangeStart) .... I only see STRING1, STRING2 columns. NUMBER1 column is missing. However, If I change the strSQL to: Select STRING1, DIGITS(NUMBER1), STRING2 from myTable (where DIGITS is an AS400 SQL function to convert number to string) Then I can see all 3 columns. Is there any QueryTable property that I need to change, so I can get all column without converting anythign to string. Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Column Using QueryTables.Add
So first, rs.fields.count=2 ?
Then using DIGITS, rs.fields.count=3 ? NickHK "Tieu" wrote in message ... Hi, I am having problem with QueryTables.Add as follow ... strSQL = "Select STRING1, NUMBER1, STRING2 from myTable" Set rs = cn.Execute(strSQL) ... Set qTable = ActiveSheet.QueryTables.Add( rs, RangeStart) ... I only see STRING1, STRING2 columns. NUMBER1 column is missing. However, If I change the strSQL to: Select STRING1, DIGITS(NUMBER1), STRING2 from myTable (where DIGITS is an AS400 SQL function to convert number to string) Then I can see all 3 columns. Is there any QueryTable property that I need to change, so I can get all column without converting anythign to string. Thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Column Using QueryTables.Add
the rs.fields is 3 in both cases. The problem is at the QueryTable. When
adding the rs to QTable, somehow the 2 numeric fields didn't get added over. Thanks. Tieu So first, rs.fields.count=2 ? Then using DIGITS, rs.fields.count=3 ? NickHK Hi, I am having problem with QueryTables.Add as follow ... strSQL = "Select STRING1, NUMBER1, STRING2 from myTable" Set rs = cn.Execute(strSQL) ... Set qTable = ActiveSheet.QueryTables.Add( rs, RangeStart) ... I only see STRING1, STRING2 columns. NUMBER1 column is missing. However, If I change the strSQL to: Select STRING1, DIGITS(NUMBER1), STRING2 from myTable (where DIGITS is an AS400 SQL function to convert number to string) Then I can see all 3 columns. Is there any QueryTable property that I need to change, so I can get all column without converting anythign to string. Thanks in advance. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Column Using QueryTables.Add
Well, if you 3 fields in your recordset, I don't see how it can disappear
from the WS. What do you get if you do not use an RS but put it directly into a QueryTable ? NickHK "Tieu" wrote in message ... the rs.fields is 3 in both cases. The problem is at the QueryTable. When adding the rs to QTable, somehow the 2 numeric fields didn't get added over. Thanks. Tieu So first, rs.fields.count=2 ? Then using DIGITS, rs.fields.count=3 ? NickHK Hi, I am having problem with QueryTables.Add as follow ... strSQL = "Select STRING1, NUMBER1, STRING2 from myTable" Set rs = cn.Execute(strSQL) ... Set qTable = ActiveSheet.QueryTables.Add( rs, RangeStart) ... I only see STRING1, STRING2 columns. NUMBER1 column is missing. However, If I change the strSQL to: Select STRING1, DIGITS(NUMBER1), STRING2 from myTable (where DIGITS is an AS400 SQL function to convert number to string) Then I can see all 3 columns. Is there any QueryTable property that I need to change, so I can get all column without converting anythign to string. Thanks in advance. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Missing Column Using QueryTables.Add
The QueryTable has been always worked correctly. However, there's a reason
that I have to use ADO. I created a SQL UDF which calls a RPG program inside AS400. This has something to do with user access. And since I already got my QueryTable setup, I just want to pass ADO to QueryTable via Add Method. Anyway, I guess I will have to convert everything to string. I would use ADO for SQL UDF and the rest on QueryTable. Thanks. Tieu Well, if you 3 fields in your recordset, I don't see how it can disappear from the WS. What do you get if you do not use an RS but put it directly into a QueryTable ? NickHK the rs.fields is 3 in both cases. The problem is at the QueryTable. When adding the rs to QTable, somehow the 2 numeric fields didn't get added over. Thanks. Tieu So first, rs.fields.count=2 ? Then using DIGITS, rs.fields.count=3 ? NickHK Hi, I am having problem with QueryTables.Add as follow ... strSQL = "Select STRING1, NUMBER1, STRING2 from myTable" Set rs = cn.Execute(strSQL) ... Set qTable = ActiveSheet.QueryTables.Add( rs, RangeStart) ... I only see STRING1, STRING2 columns. NUMBER1 column is missing. However, If I change the strSQL to: Select STRING1, DIGITS(NUMBER1), STRING2 from myTable (where DIGITS is an AS400 SQL function to convert number to string) Then I can see all 3 columns. Is there any QueryTable property that I need to change, so I can get all column without converting anythign to string. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
missing column | Excel Discussion (Misc queries) | |||
Help - Column D is missing. | Excel Worksheet Functions | |||
Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
QueryTables Add | Excel Programming |