select fields names with "dot"

G

guy

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:=varConn2,
Destination:=Worksheets("test").Range("a1"), Sql:=varSql2)

Worksheets("test").Range("A:IV").ClearContents

varQry2.BackgroundQuery = False
varQry2.Refresh

End Sub
 
G

guy

still failed...

INTP56 said:
Try putting square brackets around ship.date, as in [ship.date]

Bob

guy said:
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:=varConn2,
Destination:=Worksheets("test").Range("a1"), Sql:=varSql2)

Worksheets("test").Range("A:IV").ClearContents

varQry2.BackgroundQuery = False
varQry2.Refresh

End Sub
 
I

INTP56

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 said:
still failed...

INTP56 said:
Try putting square brackets around ship.date, as in [ship.date]

Bob

guy said:
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:=varConn2,
Destination:=Worksheets("test").Range("a1"), Sql:=varSql2)

Worksheets("test").Range("A:IV").ClearContents

varQry2.BackgroundQuery = False
varQry2.Refresh

End Sub
 

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

Top