CODEWITHSUNDEEP
vbaexcel
vijay kumar
vijay kumar

Reputation: 222

Using Cell reference in SQL query

I am using below VBA command to extract data using a SQL query by using a cell reference as filter.

Public Sub Run()

    Dim SQL As String
    Dim Connected As Boolean
    Dim server_name As String
    Dim database_name As String
    Dim Allocation As String

    Sheets("Perc").Select
    Range("A6").Select
    Selection.CurrentRegion.Select
    Selection.ClearContents


    ' Our query
    SQL = "SELECT Segmentation_ID,MPG,SUM(Segmentation_Percent) AS PERC " & _
          "FROM dbo.HFM_ALLOCATION_RATIO " & _
          "WHERE Segmentation_ID = " & Sheets("Perc").Range("A1").Value & " " & _
          "GROUP BY Segmentation_ID,MPG ORDER BY MPG"

    ' Connect to the database
    server_name = Sheets("General").Range("D1").Value
    database_name = Sheets("General").Range("G1").Value
    Connected = Connect(server_name, database_name)

    If Connected Then
        ' If connected run query and disconnect
        Call Query(SQL)
        Call Disconnect
    Else
        ' Couldn't connect
        MsgBox "Could Not Connect!"
    End If

End Sub

But when I run the macro, it's showing a runtime error

Invalid column name ALO0000274

Here ALO0000274 is the filter I set in A1.

Kindly help to fix this error.

Upvotes: 2

Views: 4836

Answers (2)

gwhenning
gwhenning

Reputation: 138

I don't see where you are adding your single quotes around your string. Have you tried changing your WHERE clause portion of your SQL statement to "WHERE Segmentation_ID = '" & Sheets("Perc").Range("A1").Value & "' "

Upvotes: 1

Barranka
Barranka

Reputation: 21047

Add quotes to your where clause (you're passing a text value, so you need to include quotes):

SQL = "SELECT Segmentation_ID,MPG,SUM(Segmentation_Percent) AS PERC " & _
      "FROM dbo.HFM_ALLOCATION_RATIO " & _
      "WHERE Segmentation_ID = '" & Sheets("Perc").Range("A1").Value & "' " & _
      "GROUP BY Segmentation_ID,MPG ORDER BY MPG"

(Note the single quotes ' that enclose the value you want to filter)

Upvotes: 2

Related Questions