How to concatenate string correctly at end of array in vba?

Question

How can i concatenate the following string to make an SQL statement?

Dim sql = "SELECT * FROM users with (nolock) WHERE"

    Dim sql2 = "(ownerID = '"

    For Each item In arrExcelValues

        sql2 = sql2 + item + "' or renterID = '" + item + "') or"

    Next

I need it to look like this;SELECT * FROM users with (nolock) WHERE (ownerID = '12554' or renterID = '12554') or (ownerID = '32433' or renterID = '32433')

So when it reaches the end of the array it will not include the "or" . Thanks for any help.

its an array holding 551 user id's - im reading these values from excel, i have debugged and they are all in the array — Aaron C, Apr 07 '16 at 14:41
It looks like you're concatenating user input into a WHERE clause. Would you like to build a paramaterized query instead? `` [obligatory xkcd](https://xkcd.com/327/) — Mathieu Guindon, Apr 07 '16 at 14:43

score 1 · Accepted Answer · edited May 23 '17 at 12:24

You could have modified the last answer I gave to your question... see here

But I've done it for you

Sub test()

    Dim strQuery As String
    Dim strVals As String

    Dim rngTarget As Range
    Set rntTarget = Range("A1:A7")

    Dim varArr
    Dim lngRow As Long
    Dim myArray()
    varArr = rntTarget.Value2

    ReDim myArray(1 To UBound(varArr, 1))

    For lngRow = 1 To UBound(varArr, 1)
        myArray(lngRow) = varArr(lngRow, 1)
    Next

    strVals = "('" & Join$(myArray, "','") & "') "

    strQuery = "SELECT * FROM users WHERE "

    For lngRow = LBound(myArray) To UBound(myArray)
        strQuery = strQuery & "( owner = '" _
            & myArray(lngRow) + "' or renterID = '" _
            & myArray(lngRow) & "') or "
    Next

    strQuery = Left(strQuery, Len(strQuery) - 4)

    Debug.Print strQuery

End Sub

How to concatenate string correctly at end of array in vba?

1 Answers1

Linked