SQL code?

[Soniya]

Hi all,
My sql statement look like this, but it dont seems to me
working either cause the statement is too long or
something else?!!
How can I fix this?

With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abcd.com;UID=User;;APP=Microsoft Office
2003;WSID=ME"
".CommandText = Array( _
"SELECT Store.arl,Store.Tktno, Store.bsp, Store.SalRef,
Store.Issuedate, Store.class, Store.status," _
"Store.Reissueno,Store.Dep_Date, Store.Route1,
Store.Route2, Store.Route3, Store.Route4, Store.Route5,
Store.Routeovrall," _
"Store.Airline1, Store.Dest, Store.PassName,
Store.acctyp," _
"Store.IRNum, Store.CSFare, Store.PubFare,
Store.PubFareus, Store.PubFare1, Store.PubFare2,
Store.PubFare3, Store.PubFare4," _
"Store.PubFare5, Store.ComFare, Store.fd, Store.My_fd,
Store.unpaid, Store.Rev, Store.tax1, Store.tax2,
Store.tax3," _
"Store.FuelSurcharge, Store.cash, Store.cc, Store.cctype,
Store.ccno, Store.ccexdate, Store.slsman,
Store.ConjOrder," _
"Store.ConjWith, Store.MyDscntVal, Store.AddColl,
Store.status1, Store.status2, Store.status3,
Store.status4," _
"Store.status5, Store.PhyLoc, Store.Target, Store.Stock,
Store.SpCom, Store.SpComVal, Store.Net2Air," _
"Store.CashReceipt, Store.DscntFare, Store.TourCode,
Store.Txi, Store.Txino, Store.Remarks," _
"Store.RGroup , Store.Posted, Store.AirCharges,
Store.AttCharges, Store.TicketType,
Store.Commissionable," _
"Store.DiscountedTicket, Store.ComShare2,
Store.ComShare3, Store.ComShare4, Store.ComShare5," _
"Store.ComShare, Store.FlightNo, Store.FlightDate, " _
" Store.ReferenceNo, Store.tax, Store.Face," _
" Store.Location FROM MeYear.dbo.Store Store WHERE
(Store.bsp='N') " _
"AND (Store.Tktno Not Like '%I%' And Store.Tktno Not
Like '%X%')" _
"AND (Store.Issuedate>={ts '2004-01-01 00:00:00'} And
Store.Issuedate<={ts '2004-01-31 00:00:00'})"


TIA
Soniya

 

[Myrna Larson]

You shouldn't have quote mark before the .CommandText part.

The SQL statement must be a single string, not an array of strings. If you are
thinking that putting an array statement inside of quotes forces the elements
of the array to be concatenated into a single string, it doesn't.

You use the & operator to concatenate strings, i.e.

..CommandText = "SELECT Store.arl,Store.Tktno, Store.bsp, Store.SalRef, " & _
Store.Issuedate, Store.class, Store.status, " & _
"Store.Reissueno,Store.Dep_Date, Store.Route1, " & _

etc.

 

[Myrna Larson]

PS: With Excel 2000 and above, if you have the strings in an array, you can
also concatenate them using the JOIN function. Check it out in Help.

 

[Jamie Collins]

The SQL statement must be a single string, not an array of strings. If you are
thinking that putting an array statement inside of quotes forces the elements
of the array to be concatenated into a single string, it doesn't.

Actually, it think it does. I just recorded some lovely VBA using a
long piece of sql query text and it was chopped up and put into an
array for me:

.CommandText = Array( _
"SELECT `Sheet1$`.MyMemoCol" & Chr(13) & "" & Chr(10) & "FROM
`C:\Yue`.`Sheet1$` `Sheet1$` WHERE `Sheet1$`.MyMemoCol =
'0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678"
_
, _
"90123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'"
_
)

Jamie.

--