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

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

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

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