1

Followed through several threads on SO and couldn't find enough information to fix my problem. One of the sheets in my workbook is a table I need to query with SQL. It's a named Table. Here's the function I wrote in VBA:

Function getPowerPoints(eventID, resultTime) As String

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    strFile = ThisWorkbook.FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    strSQL = "SELECT max(POINTS) FROM PowerPoints WHERE EVENT = " & eventID & " and TIME >= " & resultTime ''Named range

    rs.Open strSQL, cn

    getPowerPoints = rs.GetString

    cn.Close

    Set rs = Nothing
    Set cn = Nothing

End Function

When I try to use the function in my worksheet, I just get a #NAME? error with no helpful messages anywhere. How do I begin to debug this?

Eddie Rowe
  • 102
  • 2
  • 10
  • 3
    You can remove the vba from the Quotes and concatenate with `&`. like this: `"SELECT max(POINTS) FROM PowerPoints WHERE EVENT = " & eventID & " and TIME >= & " resultTime` – Scott Craner Oct 10 '16 at 17:14
  • Good catch on the query formulation. I changed that with no change in result. Edited the post to reflect changes. – Eddie Rowe Oct 10 '16 at 17:27
  • As @ScottCraner said, the issue is that the variables are not getting passed into your SQL query since they are enclosed in quotes. Essentially your query is being passed the literal "eventID" instead of the variable. Since this is a short query it may not matter but the longer your variable `strSQL` becomes the more difficult it is to debug. It may end up being beneficial to create new variables like `strSelect`, `strFrom`, `strWhere`, etc and then concatenating them to your `strSQL`. – J_Lard Oct 10 '16 at 17:30
  • @J_Lard - our comments must have crossed in cyber-space. I noticed that mistake that Scott pointed out and corrected it. The error persists. – Eddie Rowe Oct 11 '16 at 14:06
  • [link]http://stackoverflow.com/questions/5349580/compiler-error-user-defined-types-not-defined[/link] By referencing this question, I set the reference and was able to compile, but I'm still not getting past the `#NAME?` error. – Eddie Rowe Oct 12 '16 at 14:19

1 Answers1

0

You are missing the ' and " see below

strSQL = "SELECT max(POINTS)
         FROM PowerPoints 
         WHERE EVENT = ' " & eventID & " ' 
         and 
         TIME >= ' " & resultTime " ' "
Akhila
  • 3,235
  • 1
  • 14
  • 30
Jim
  • 1