| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select fields names with "dot"
How to select field names which contain a dot "."?
Error comes out when running the following codes, i guess it is caused by the field name "ship.date" Would you pls advise... Thanks a lot!! ______________________________________________ Sub getFields() Dim varConn2, varSql2 As String Dim varQry2 As QueryTable varConn2 = "ODBC;DefaultDir=c:\Test;driver={Microsoft Excel Driver (*.xls)};DriverId=790;dbq=c:\Test\table1.xls" varSql2 = "SELECT PRODUCT, [Unit Price] as UNIT_PRICE, ship.date, sum([Open Qty]) as SUM_OPEN_QTY from [Raw$] group by Product, [Unit Price], ship.date" Set varQry2 = Worksheets("test").QueryTables.Add(Connection:=var Conn2, Destination:=Worksheets("test").Range("a1"), Sql:=varSql2) Worksheets("test").Range("A:IV").ClearContents varQry2.BackgroundQuery = False varQry2.Refresh End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select fields names with "dot"
Try putting square brackets around ship.date, as in [ship.date]
Bob "guy" wrote: How to select field names which contain a dot "."? Error comes out when running the following codes, i guess it is caused by the field name "ship.date" Would you pls advise... Thanks a lot!! ______________________________________________ Sub getFields() Dim varConn2, varSql2 As String Dim varQry2 As QueryTable varConn2 = "ODBC;DefaultDir=c:\Test;driver={Microsoft Excel Driver (*.xls)};DriverId=790;dbq=c:\Test\table1.xls" varSql2 = "SELECT PRODUCT, [Unit Price] as UNIT_PRICE, ship.date, sum([Open Qty]) as SUM_OPEN_QTY from [Raw$] group by Product, [Unit Price], ship.date" Set varQry2 = Worksheets("test").QueryTables.Add(Connection:=var Conn2, Destination:=Worksheets("test").Range("a1"), Sql:=varSql2) Worksheets("test").Range("A:IV").ClearContents varQry2.BackgroundQuery = False varQry2.Refresh End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select fields names with "dot"
still failed...
"INTP56" ... Try putting square brackets around ship.date, as in [ship.date] Bob "guy" wrote: How to select field names which contain a dot "."? Error comes out when running the following codes, i guess it is caused by the field name "ship.date" Would you pls advise... Thanks a lot!! ______________________________________________ Sub getFields() Dim varConn2, varSql2 As String Dim varQry2 As QueryTable varConn2 = "ODBC;DefaultDir=c:\Test;driver={Microsoft Excel Driver (*.xls)};DriverId=790;dbq=c:\Test\table1.xls" varSql2 = "SELECT PRODUCT, [Unit Price] as UNIT_PRICE, ship.date, sum([Open Qty]) as SUM_OPEN_QTY from [Raw$] group by Product, [Unit Price], ship.date" Set varQry2 = Worksheets("test").QueryTables.Add(Connection:=var Conn2, Destination:=Worksheets("test").Range("a1"), Sql:=varSql2) Worksheets("test").Range("A:IV").ClearContents varQry2.BackgroundQuery = False varQry2.Refresh End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
select fields names with "dot"
I thought the [] might help, but since they don't I'm not sure there is an
answer, other than you have to get the dot out of any column you want to use in a SQL statement, because "." is used to qualify objects, as in Database.Schema.Table.Column. Bob "guy" wrote: still failed... "INTP56" ... Try putting square brackets around ship.date, as in [ship.date] Bob "guy" wrote: How to select field names which contain a dot "."? Error comes out when running the following codes, i guess it is caused by the field name "ship.date" Would you pls advise... Thanks a lot!! ______________________________________________ Sub getFields() Dim varConn2, varSql2 As String Dim varQry2 As QueryTable varConn2 = "ODBC;DefaultDir=c:\Test;driver={Microsoft Excel Driver (*.xls)};DriverId=790;dbq=c:\Test\table1.xls" varSql2 = "SELECT PRODUCT, [Unit Price] as UNIT_PRICE, ship.date, sum([Open Qty]) as SUM_OPEN_QTY from [Raw$] group by Product, [Unit Price], ship.date" Set varQry2 = Worksheets("test").QueryTables.Add(Connection:=var Conn2, Destination:=Worksheets("test").Range("a1"), Sql:=varSql2) Worksheets("test").Range("A:IV").ClearContents varQry2.BackgroundQuery = False varQry2.Refresh End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do you select many fields in a pivot table to drag in "data"? | Excel Discussion (Misc queries) | |||
| "Control" plus "click" doesn't allow me to select multiple cells | New Users to Excel | |||
| Backup to specific folder if workbook names begins with "NSR" or "MAC" | Excel Programming | |||
| insert/names/apply - how to "select all" ? | Excel Programming | |||
| Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming | |||
Linear Mode
