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
– Aaron C, Commented Apr 7, 2016 at 14:41
It looks like you're concatenating user input into a WHERE clause. Would you like to build a paramaterized query instead? </clippy> obligatory xkcd — Mathieu Guindon
– Mathieu Guindon, Commented Apr 7, 2016 at 14:43

Community · Accepted Answer · 2017-05-23 12:24:05Z

1

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

edited May 23, 2017 at 12:24

CommunityBot

11 silver badge

answered Apr 7, 2016 at 14:45

user6028892

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related