CODEWITHSUNDEEP
vbaexcelexcel-2010ms-access-2010
ColdTeck
ColdTeck

Reputation: 143

Quotations in Access String from Excel VBA

Ok I am having a Problem using VBA from Excel 2010 to Query data in access, the problem comes when the variable Descripcheck, or Grouplocal, some of the descriptions have a "" in the excel cell so when it pulls the string itself this causes the query function gets a syntax error. Any ideas?

            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

Upvotes: 1

Views: 315

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123429

If you keep fooling around with those "s and Chr$(34)s you'll drive yourself crazy. Try using a parameterized query instead. Consider the following (simplified) example. It uses some test data in 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

...an Excel sheet that looks like this...

Excel.png

...and the code behind the button is

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

Upvotes: 2

Related Questions