locked
Syntax Concatenating 2 Fields in VBA SQL RRS feed

  • Question

  • My workbook has two sheets with data - "Recon Detail" and "DB".

    I'm using the connection string "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & "DBQ=" & ThisWorkbook.FullName & ";" so that I can write and execute SQL against these two sheets..

    The query works. However, I need the first two fields (T02.[Do Ty] and T02.[Document Number]) in the SQL statement combined with a dash between the two. The first field is alpha, and the second field is numeric. The results would look like DC-987654.

    Google is usually my friend, but not this time. I've tried using ||, +, &, Concat, and Coalesce. Can someone help me with the syntax to do this? My code:

    Private Sub TestDocNums()
        Sheets("Sheet1").Select
            Range("A1").Select
        
        strXLConnection = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
            & "DBQ=" & ThisWorkbook.FullName & ";"

        Set rsQuery = New ADODB.Recordset
        
        strSQL = "Select T02.[Do Ty], T02.[Document Number], T02.[Amt Open] " _
            & "From [Recon Detail$A:E] AS T01, [DB$A:T] AS T02 " _
            & "Where T01.[Order Number] = T02.[Order Number] AND " _
            & "T01.[Line Number] = T02.[Line Number]"

        'Copy data to result sheet
        rsQuery.Open strSQL, strXLConnection, adOpenForwardOnly, adLockReadOnly, adCmdText

        Range("A1").Select
        For x = 0 To rsQuery.Fields.Count - 1
            ActiveCell.Offset(0, x).Value = rsQuery.Fields(x).Name
        Next x

        ActiveCell.Offset(1, 0).Select

        ActiveCell.CopyFromRecordset rsQuery
        Set rsQuery = Nothing
    End Sub

    Thanks in advance,

    Mike

    Wednesday, September 24, 2014 2:59 PM

Answers

  • Try

        strSQL = "Select T02.[Do Ty] & ""-"" & T02.[Document Number], T02.[Amt Open] " _
             & "From [Recon Detail$A:E] AS T01, [DB$A:T] AS T02 " _
             & "Where T01.[Order Number] = T02.[Order Number] AND " _
             & "T01.[Line Number] = T02.[Line Number]"

    Note the doubling of the " within the string.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by danishani Thursday, September 25, 2014 3:42 AM
    • Marked as answer by danishani Tuesday, September 30, 2014 5:38 PM
    Wednesday, September 24, 2014 3:23 PM