Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL Code
Hi All,
I hav the follwing code (from KeepITcool) to get data from two tables (cash & Credit)to my sheet1. In the third Table (Rev) Tktno, SalRef are key fields and I want get from that table ItemA, ItemB and ItemC corresponding to Tktno, so my data will be from both table cash and credit with additional fields from table Rev. both cash and credit Tktno are included in Table "Rev". How can I modify my following code to get this. I am very new to SQL (import data) and this is my first attempt. Dim sCmd() Dim i% ReDim sCmd(8) sCmd(0) = _ " SELECT " & _ " c.arl, c.Tktno, c.bsp,c.SalRef, c.Issuedate," & _ " c.class, c.Reissueno, c.PassName, c.IRNum, " & _ " c.PubFare, c.ComFare, c.fd, c.A_fd, c.unpaid,c.Rev," & _ " c.tax1, c.tax2, c.tax3, c.slsman, " sCmd(1) = _ " c.AddColl, c.AttrDscntVal, c.Target, c.Stock, " & _ " c.SpComVal, c.Net2Air, c.TourCode, " & _ " c.Remarks, c.Commissionable, c.prov, c.TicketType" & vbCrLf sCmd(2) = _ " FROM abcd.dbo.Cash c" & vbCrLf sCmd(3) = _ " WHERE (c.bsp<'U') AND (c.Tktno Not Like '%I%')" & _ " AND (c.Issuedate={ts '" & Dt1 & " 00:00:00'} " & _ " AND c.Issuedate<={ts '" & Dt2 & " 00:00:00'})" & vbCrLf sCmd(4) = " UNION " & vbCrLf sCmd(5) = sCmd(0) sCmd(6) = sCmd(1) sCmd(7) = Replace(sCmd(2), ".Cash", ".Credit") sCmd(8) = sCmd(3) For i = 0 To 8 Debug.Assert Len(sCmd(i)) <= 255 Next Sheets(sh).Visible = True Sheets(sh).Select Range("A1").Select With Selection.QueryTable .Connection = _ "ODBC;DRIVER=SQL Server;SERVER=abc.com;UID=User;" & _ "PWD=pwd;APP=Microsoft Office 2003;WSID=USER" .CommandText = sCmd .Refresh BackgroundQuery:=False End With TIA Soniya |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
option buttons run Click code when value is changed via VBA code | Excel Programming | |||
VBA code delete code but ask for password and unlock VBA protection | Excel Programming |