Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to change where query results start?

Status
Not open for further replies.

alwayslrN

IS-IT--Management
Jun 20, 2006
159
US
I have the following in a module. The results start in A5, is there anything in the code I can change so that the results start in A6 instead?

Code:
Sub sumQry()
'
' sumQry Macro
' Macro recorded 6/7/2007
'
' Keyboard Shortcut: Ctrl+q
'
Dim sSQL As String
Dim acctg_date As Date
Dim xl As Application
    
Set xl = Application

acctg_date = wsSummary.Range("A3").Value

sSQL = "SELECT "
sSQL = sSQL & "nyl_sfp_poolmast.deal,"
sSQL = sSQL & "nyl_sfp_poolmast.pool,"
sSQL = sSQL & "pool_prin_bal = isnull(b.pool_prin_bal,0),"
sSQL = sSQL & "pool_original_amt = isnull(b.pool_original_amt,0),"
sSQL = sSQL & "pool_funding_amt = isnull(b.pool_funding_amt,0),"
sSQL = sSQL & "loan_count = isnull(b.loan_count,0),"
sSQL = sSQL & "percentage = isnull(b.pool_prin_bal/p.all_pools_prin_bal,0),"
sSQL = sSQL & "cur_ltv = isnull(b.cur_ltv,0),"
sSQL = sSQL & "gross_rate = isnull(b.gross_rate,0),"
sSQL = sSQL & "service_fee = isnull(b.service_fee,0),"
sSQL = sSQL & "net_rate= isnull(b.net_rate,0),"
sSQL = sSQL & "orig_term = isnull(b.orig_term,0),"
sSQL = sSQL & "cur_rterm = isnull(b.cur_rterm,0),"
sSQL = sSQL & "cur_age = isnull(b.cur_age,0),"
sSQL = sSQL & "b.due_day"
sSQL = sSQL & vbCrLf
sSQL = sSQL & "FROM"
sSQL = sSQL & " LMS_NYL.dbo.nyl_sfp_poolmast nyl_sfp_poolmast"
sSQL = sSQL & vbCrLf
sSQL = sSQL & "left outer join (select pool, pool_prin_bal = sum(prin_bal),"
sSQL = sSQL & "pool_original_amt = sum(original_amt),"
sSQL = sSQL & "pool_funding_amt = sum(funding_amt),"
sSQL = sSQL & "cur_ltv = round((case sum(prin_bal) when 0 then 0 else sum(wcltv)/sum(prin_bal) end),2),"
sSQL = sSQL & "gross_rate = round((case sum(prin_bal) when 0 then 0 else sum(wac)/sum(prin_bal) end),3),"
sSQL = sSQL & "service_fee = round((case sum(prin_bal) when 0 then 0 else sum(wcsfee)/sum(prin_bal) end),4),"
sSQL = sSQL & "net_rate = round(((case sum(prin_bal) when 0 then 0 else sum(wac)/sum(prin_bal) end)- (case sum(prin_bal) when 0 then 0 else sum(wcsfee)/sum(prin_bal) end)),3),"
sSQL = sSQL & "orig_term = (case sum(prin_bal) when 0 then 0 else sum(wcoterm)/sum(prin_bal) end),"
sSQL = sSQL & "cur_rterm = (case sum(prin_bal) when 0 then 0 else sum(wcrterm)/sum(prin_bal) end),"
sSQL = sSQL & "cur_age = (case sum(prin_bal) when 0 then 0 else sum(wage)/sum(prin_bal) end),"
sSQL = sSQL & "due_day = (case sum(prin_bal) when 0 then 0 else sum(wcremit)/sum(prin_bal) end),"
sSQL = sSQL & "loan_count = count(case when prin_bal != 0 then 1 end),"
sSQL = sSQL & "hist_date"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " from nyl_sfp_balances"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where hist_date = '" & acctg_date & "'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " group by pool,hist_date) b on"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " b.pool = nyl_sfp_poolmast.pool"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " left outer join (select hist_date,all_pools_prin_bal = sum(prin_bal)"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " from nyl_sfp_balances"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where hist_date = '" & acctg_date & "'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " group by hist_date) p on"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " p.hist_date = '" & acctg_date & "'"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " where nyl_sfp_poolmast.closed_date <= '" & acctg_date & "'" & " or nyl_sfp_poolmast.closed_date is null"
sSQL = sSQL & vbCrLf
sSQL = sSQL & " ORDER BY"
sSQL = sSQL & " nyl_sfp_poolmast.pool ASC"

'clear existing records in case user chooses an earlier accounting date
wsSummary.Range("sum_table").Select
xl.DisplayAlerts = False
Application.ScreenUpdating = False
Selection.ClearContents
xl.DisplayAlerts = True
Application.ScreenUpdating = True


    With wsSummary.QueryTables(1)
        .Connection = _
        "ODBC;DSN=LMS_NYL;UID=report;PWD=nylim;APP=Microsoft Office XP;WSID=IMN-FMO-TXKV5;DATABASE=LMS_NYL" _
        '), Array("ed_Connection=Yes"))
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
    Range("G6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00%"
    Range("I6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "0.00000000"

End Sub
 
With wsSummary.QueryTables(1)
.Destination = wsSummary.Range("A6")
.Connection = _
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks PH - that didn't work, suprisingly.
 
And this ?
.Destination = "A6"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 




Hi,

The Destination property of the QueryTable object is READ ONLY.

You can CUT and the PASTE the QueryTable
Code:
    wsSummary.QueryTables(1).ResultRange.Cut
    wsSummary.Paste wsSummary.Range("A6")

Skip,

[glasses] [red][/red]
[tongue]
 
BTW, why not simply insert a row after A4 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 




Method A or method B.

As one surgeon said to the other, "Suture self!" ;-)


Skip,

[glasses] [red][/red]
[tongue]
 
Thanks Skip and PH. I ended up using the method given by Skip. I had to do some minor tweeks in other areas, but it worked fine after that.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top