Excel VBA中访问字符串中的报价

ColdTeck 发表于 Dev

ColdTeck

好的，我在使用Excel 2010中的VBA来查询访问中的数据时遇到问题，当变量Descripcheck或Grouplocal的某些描述在excel单元格中带有“”时，就会出现问题，因此当它拉字符串本身时，这会导致查询函数获取语法错误。有任何想法吗？

            PMnum = Cells(B, 3)
            Grouplocal = Cells(B, 4)
            Descripcheck = Cells(B, 6)
            DevTyp = Cells(B, 5)
            TagName = Cells(B, 2)

            If PMnum = "" Then
            PMnum = " IS NULL"
            Else:
            PMnum = "=" & PMnum
            End If

            If Grouplocal = "" Then
            Grouplocal = " IS NULL"
            Else:
            Grouplocal = "=" & Chr$(34) & Grouplocal & Chr$(34)
            End If

            If Descripcheck = "" Then
            Descripcheck = " IS NULL"
            Else:
            Descripcheck = "=" & Chr$(34) & Descripcheck & Chr$(34)
            End If

            If DevTyp = "" Then
            DevTyp = " IS NULL"
            Else:
            DevTyp = "=" & Chr$(34) & DevTyp & Chr$(34)
            End If

            If TagName = "" Then
            TagName = " IS NULL"
            Else:
            TagName = "=" & Chr$(34) & TagName & Chr$(34)
            End If

            sCmndString = "SELECT Site_Data.Pass_Fail, Site_Data.Tag_Name, Site_Data.[PM_#],Site_Data.Group_Location_Reference, Site_Data.Device_Type, Site_Data.Description, Site_Data.Set_Point, Site_Data.Set_Point_Units, Site_Data.Fail_Low, Site_Data.Fail_High, Site_Data.As_Found, Site_Data.As_Left, Site_Data.Manufacturer_SN, Site_Data.Year_Put_Into_Service, Site_Data.Date_of_Test, Site_Data.Time_To_Complete, Site_Data.Service, Site_Data.Comments, Site_Data.Site, Site_Data.Year, Site_Data.Month " & _
                  "FROM Site_Data WHERE (((Site_Data.[PM_#])" & PMnum & ") AND " & _
                  "((Site_Data.Group_Location_Reference)" & Grouplocal & ") AND " & _
                  "((Site_Data.Device_Type)" & DevTyp & ") AND " & _
                  "((Site_Data.Description)" & Descripcheck & ") AND " & _
                  "((Site_Data.Site)=" & Chr$(34) & SiteName & Chr$(34) & ") AND " & _
                  "((Site_Data.Year)=" & Chr$(34) & yrs & Chr$(34) & ") AND " & _
                  "((Site_Data.Month)=" & Chr$(34) & Mnth & Chr$(34) & ") AND " & _
                  "((Site_Data.Tag_Name)" & TagName & "));"
            Set rs = New ADODB.Recordset
            rs.Open sCmndString, cnt, 2, 3, 1

戈德·汤普森

如果您不停地与这些"s和Chr$(34)s混蛋，您将使自己发疯。尝试改用参数化查询。考虑以下（简化的）示例。它使用Access中的一些测试数据...

Site_ID  Device_Type  Description 
-------  -----------  ------------
      1  Type1        test1       
      2  Type1                    
      3  Type1                    
      4  Type2        "some" value
      5  Type2        "some" value
      6  Type2                    
      7  Type2                    
      8  Type2

看起来像这样的Excel工作表

...按钮后面的代码是

Option Explicit

Public Sub AccessLookup()
    Dim con As ADODB.Connection, cmd As ADODB.Command, rst As ADODB.Recordset
    Dim DevTyp As Variant, Descripcheck As Variant
    Dim s As String, i As Long

    s = Trim(CStr(Range("B1").Value))
    DevTyp = IIf(Len(s) = 0, Null, s)
    s = Trim(CStr(Range("B2").Value))
    Descripcheck = IIf(Len(s) = 0, Null, s)

    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Public\Database1.accdb;"
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandText = _
            "SELECT COUNT(*) AS n FROM Site_Data " & _
            "WHERE Device_Type " & IIf(IsNull(DevTyp), "IS NULL ", "= ? ") & _
                "AND Description " & IIf(IsNull(Descripcheck), "IS NULL ", "= ? ")
    i = 0
    If Not IsNull(DevTyp) Then
        cmd.CreateParameter "?", adVarWChar, adParamInput, 255
        cmd.Parameters(i).Value = DevTyp
        i = i + 1
    End If
    If Not IsNull(Descripcheck) Then
        cmd.CreateParameter "?", adVarWChar, adParamInput, 255
        cmd.Parameters(i).Value = Descripcheck
        i = i + 1
    End If
    Set rst = cmd.Execute
    Range("B6").Value = rst("n").Value
    rst.Close
    Set rst = Nothing
    Set cmd = Nothing
    con.Close
    Set con = Nothing
End Sub