如何在vba中的数组末尾正确连接字符串? [英] How to concatenate string correctly at end of array in vba?

问题描述

如何连接以下字符串以形成SQL语句?

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

我需要它看起来像这样; SELECT *来自具有(nolock)WHERE(ownerID ='12554'或renterID ='12554')或(ownerID ='32433'或renterID ='32433')的用户

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

因此，当它到达数组的末尾时，将不包含"or".谢谢你的帮助.

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

推荐答案

您可能已经修改了我对您的问题给出的最后一个答案...

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

但是我已经为你做到了

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

这篇关于如何在vba中的数组末尾正确连接字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！