Results 1 to 4 of 4
  1. #1
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1

    Question Unanswered: Help With VBA Code "Union"

    Hi I need some help in getting my head around this.

    Firstly I hope I am in the right section for this question is to do with Access vs the difference in Excel, Both applications Ver 2007

    Ok in Access I have created a Union join in Sql, which works fine
    Code:
    select Request_ID, LV.Value_Desc As WinProb, Credit_Outcome, DirectorApproval_Outcome, MovementInfoAvailable 
            FROM vwDetailedRequests
            INNER JOIN ListValues LV ON WinProbability = LV.Value
            where status_desc like 'Open%'and LV.List_ID='17'
            
     Union      
            
    select Request_ID,Cast(WinProbability As NVARCHAR) As WinProb, Credit_Outcome, DirectorApproval_Outcome, MovementInfoAvailable
            FROM vwDetailedRequests
            where status_desc like 'Open%'and WinProbability='0'
    But when I replicate it in Excel the Union no longer works,

    Code:
    Public Sub GetTenderDetail2()
    
    Dim conSQL As ADODB.Connection, rs As ADODB.Recordset, strSQL As String, tmpArray() As Variant
             
    Set conSQL = GetSQLConnection("Provider=*****;Data Source=*******;database=tenders;trusted_connection=yes")
    
    Set rs = CreateObject("ADODB.Recordset")
    
    strSQL = "select Request_ID, LV.Value_Desc As WinProb, Credit_Outcome, DirectorApproval_Outcome, MovementInfoAvailable " & _
            "FROM vwDetailedRequests  " & _
            "INNER JOIN ListValues LV ON WinProbability = LV.Value " & _
            "where status_desc like 'Open%'and LV.List_ID='17'"
            
            Union
            
    strSQL = "select Request_ID, Cast(WinProbability As NVARCHAR) As WinProb, Credit_Outcome, DirectorApproval_Outcome, MovementInfoAvailable " & _
            "FROM vwDetailedRequests  " & _
            "where status_desc like 'Open%'and WinProbability='0'"
           
    rs.Open strSQL, conSQL
    
    RowNo = 5
    Do Until rs.EOF = True
        For ColNo = 26 To 30
        CurrentMonth.Cells(RowNo, ColNo) = Trim(Mid(rs(ColNo - 26), 1, 32767))
        
        Next
        RowNo = RowNo + 1
        rs.MoveNext
    Loop
    
    rs.Close
    
    End Sub

    from looking around I derive that in Excel its looking for Ranges, but I do not understand how to declare my sql strings as ranges, the connection to the server is fine. And I know the two individual strSQL work fine independently

    Thanks
    Last edited by MarkWhyte; 05-16-12 at 05:50. Reason: Forgot to put Question Icon

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    strSQL = "select Request_ID, LV.Value_Desc As WinProb, Credit_Outcome, DirectorApproval_Outcome, MovementInfoAvailable " & _
            "FROM vwDetailedRequests  " & _
            "INNER JOIN ListValues LV ON WinProbability = LV.Value " & _
            "where status_desc like 'Open%'and LV.List_ID='17'"
            
    strSQL = strSQL & " Union " ' Mind the spaces!!!
            
    strSQL = strSQL & "select Request_ID, Cast(WinProbability As NVARCHAR) As WinProb, Credit_Outcome, DirectorApproval_Outcome, MovementInfoAvailable " & _
            "FROM vwDetailedRequests  " & _
            "where status_desc like 'Open%'and WinProbability='0'"
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Posts
    125
    Provided Answers: 1
    Once again Sinndho

    Thank you very much, could not see the woods for the trees as they say

    Works fine


  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •