S
Soniya
Hi All,
The follwing is the query code what i use in my sheet1
and sheet2
Can I combine this code and get both data in my sheet1
instead of sheet1 and sheet2?
I am taking identical fields from both tables as these
tables are kept for cash and credit and there are a few
un identical fields in these two tables.
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microsoft
Office 2003;WSID=mycom"
..CommandText = Array( _
"SELECT Cash.arl, Cash.Tktno, Cash.tkttype, Cash.bsp,
Cash.SalRef, Cash.Issuedate, Cash.class, Cash.Reissueno,
Cash.Routeovrall, Cash.PassName, Cash.IRNum, Cash.P" _
, _
"ubFare, Cash.ComFare, Cash.fd, Cash.My_fd, Cash.unpaid,
Cash.Rev, Cash.tax1, Cash.tax2, Cash.tax3,
Cash.FuelSurcharge, Cash.slsman, " _
, _
"Cash.Txino, Cash.AddColl, Cash.MyDscntVal, Cash.Target,
Cash.Stock, Cash.SpComVal, Cash.Net2Air,
Cash.CashReceipt, Cash.TourCode, " _
, _
"Cash.Remarks, Cash.Commissi" _
, _
"onable, Cash.Face, Cash.prov" & Chr(13) & "" & Chr(10)
& "FROM Attar_Travel_2004.dbo.Cash Cash" & Chr(13) & "" &
Chr(10) & "WHERE (Cash.SalRef<>'U') AND (Cash.Tktno Not
Like '%I%')" _
, _
" AND (Cash.Issuedate>={ts '" & Dt1 & " 00:00:00'} And
Cash.Issuedate<={ts '" & Dt2 & " 00:00:00'})")
..Refresh BackgroundQuery:=False
End With
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microsoft
Office 2003;WSID=mycom"
.CommandText = Array( _
"SELECT Credit.arl, Credit.Tktno, Credit.tkttype,
Credit.bsp, Credit.SalRef, Credit.Issuedate,
Credit.class, Credit.Reissueno, Credit.Routeovrall,
Credit.PassName, Credit.IRNum, Credit.P" _
, _
"ubFare, Credit.ComFare, Credit.fd, Credit.My_fd,
Credit.unpaid, Credit.Rev, Credit.tax1, Credit.tax2,
Credit.tax3, Credit.FuelSurcharge, Credit.slsman, " _
, _
"Credit.Txino, Credit.AddColl, Credit.MyDscntVal,
Credit.Target, Credit.Stock, Credit.SpComVal,
Credit.Net2Air, Credit.CreditReceipt, Credit.TourCode, " _
, _
"Credit.Remarks, Credit.Commissi" _
, _
"onable, Credit.Face, Credit.prov" & Chr(13) & ""
& Chr(10) & "FROM Attar_Travel_2004.dbo.Credit Credit" &
Chr(13) & "" & Chr(10) & "WHERE (Credit.SalRef<>'U') AND
(Credit.Tktno Not Like '%I%')" _
, _
" AND (Credit.Issuedate>={ts '" & Dt1 & "
00:00:00'} And Cedit.Issuedate<={ts '" & Dt2 & "
00:00:00'})")
.Refresh BackgroundQuery:=False
End With
TIA
Soniya
The follwing is the query code what i use in my sheet1
and sheet2
Can I combine this code and get both data in my sheet1
instead of sheet1 and sheet2?
I am taking identical fields from both tables as these
tables are kept for cash and credit and there are a few
un identical fields in these two tables.
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microsoft
Office 2003;WSID=mycom"
..CommandText = Array( _
"SELECT Cash.arl, Cash.Tktno, Cash.tkttype, Cash.bsp,
Cash.SalRef, Cash.Issuedate, Cash.class, Cash.Reissueno,
Cash.Routeovrall, Cash.PassName, Cash.IRNum, Cash.P" _
, _
"ubFare, Cash.ComFare, Cash.fd, Cash.My_fd, Cash.unpaid,
Cash.Rev, Cash.tax1, Cash.tax2, Cash.tax3,
Cash.FuelSurcharge, Cash.slsman, " _
, _
"Cash.Txino, Cash.AddColl, Cash.MyDscntVal, Cash.Target,
Cash.Stock, Cash.SpComVal, Cash.Net2Air,
Cash.CashReceipt, Cash.TourCode, " _
, _
"Cash.Remarks, Cash.Commissi" _
, _
"onable, Cash.Face, Cash.prov" & Chr(13) & "" & Chr(10)
& "FROM Attar_Travel_2004.dbo.Cash Cash" & Chr(13) & "" &
Chr(10) & "WHERE (Cash.SalRef<>'U') AND (Cash.Tktno Not
Like '%I%')" _
, _
" AND (Cash.Issuedate>={ts '" & Dt1 & " 00:00:00'} And
Cash.Issuedate<={ts '" & Dt2 & " 00:00:00'})")
..Refresh BackgroundQuery:=False
End With
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER=SQL
Server;SERVER=abc.com;UID=User;PWD=pwd;APP=Microsoft
Office 2003;WSID=mycom"
.CommandText = Array( _
"SELECT Credit.arl, Credit.Tktno, Credit.tkttype,
Credit.bsp, Credit.SalRef, Credit.Issuedate,
Credit.class, Credit.Reissueno, Credit.Routeovrall,
Credit.PassName, Credit.IRNum, Credit.P" _
, _
"ubFare, Credit.ComFare, Credit.fd, Credit.My_fd,
Credit.unpaid, Credit.Rev, Credit.tax1, Credit.tax2,
Credit.tax3, Credit.FuelSurcharge, Credit.slsman, " _
, _
"Credit.Txino, Credit.AddColl, Credit.MyDscntVal,
Credit.Target, Credit.Stock, Credit.SpComVal,
Credit.Net2Air, Credit.CreditReceipt, Credit.TourCode, " _
, _
"Credit.Remarks, Credit.Commissi" _
, _
"onable, Credit.Face, Credit.prov" & Chr(13) & ""
& Chr(10) & "FROM Attar_Travel_2004.dbo.Credit Credit" &
Chr(13) & "" & Chr(10) & "WHERE (Credit.SalRef<>'U') AND
(Credit.Tktno Not Like '%I%')" _
, _
" AND (Credit.Issuedate>={ts '" & Dt1 & "
00:00:00'} And Cedit.Issuedate<={ts '" & Dt2 & "
00:00:00'})")
.Refresh BackgroundQuery:=False
End With
TIA
Soniya