Microsoft Excel - SQL Syntax Error in my macro

Hello,

I am trying to create a macro where I can query a table based on cell values in an excel spreadsheet.  I recorded myself pulling the data, and then tried to replace the line in the code to reference the excel inputs.  When I run, I get a "Run-Time '1004': SQL Syntax Error", with line .Refresh BackgroundQuery:=False highlighted in yellow.

I am pasting below the original query recorded (which works but is not dynamic), and what I tried to change.

Recorded Query:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
      "ODBC;DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod;UID=X123456;", _
      Destination:=Range("$E$6")).QueryTable
      .CommandText = Array( _
      "SELECT table_name.field1_name, table_name.field2_name, table_name.field3_name, table_name.field4_name, table_name.field5_name, table_name.field6_name, table_name.field7_name, table_name.field8_name, table_name.field9_name, table_name.field10_name, table_namefield11_name, table_name.field12_name, table_namefield13_name, table_namefield14_name, table_namefield15_name, table_namefield16_name" & Chr(13) & "" & Chr(10) & "FROM db_firmprod" _
      , _
      ".dbo.table_name" & Chr(13) & "" & Chr(10) & "GROUP BY table_name.field1_name, table_name.field2_name, table_name.field3_name, table_name.field4_name, table_name.field5_name, table_name.field6_name, table_name.field7_name, table_name.field8_name, table_name.field9_name, table_name.field10_name, table_namefield11_name, table_name.field12_name, table_namefield13_name, table_namefield14_name, table_namefield15_name, table_namefield16_name" & Chr(13) & "" & Chr(10) & "HAVING (table_name.field1_name Like 'APPLE' Or table_name.field1_name Like 'BANANA' Or table_name.field1_name Like 'GRAPE') AND (table_name.field14={ts '2026-01-31 00:00:00'}) AND (table_name.field15_name={ts '2026-02-14 00:00:00'})" _
      )
      .RowNumbers = False
      .FillAdjacentFormulas = False
      .PreserveFormatting = True
      .RefreshOnFileOpen = False
      .BackgroundQuery = True
      .RefreshStyle = xlInsertDeleteCells
      .SavePassword = False
      .SaveData = True
      .AdjustColumnWidth = True
      .RefreshPeriod = 0
      .PreserveColumnInfo = True
      .ListObject.DisplayName = "Table_Query_from_firm_prod_1"
      .Refresh BackgroundQuery:=False
    End With

The code in green above is the code I wish to have linked to a cell in excel, because "APPLE", "BANANA", and "GRAPE" could be "HORSE", "CAT", "SNAIL", or whatever the user chooses any given day.

I tried the following;

Dim FunName As String
FunName = Range("BA5").Value

With
...code enterred as above with the exception of the green line, which now reads....."HAVING ( & FunName & ) ....
End With

the value of cell BA5 is:
table_name.field1_name Like 'APPLE' Or table_name.field1_name Like 'BANANA' Or table_name.field1_name Like 'GRAPE'

Any thoughts as to what I am doing wrong?  I am not well versed with SQL.  This issue is giving me a headache!

Thanks for your help!!!

You are sooo close.

Try this:

HAVING (" & FunName & ")

You need to break the variable out so it can be converted to text.

I made the change, but now I'm getting a type mismatch error.  Any thoughts?

Thanks for the help!

Truthfully, recorded macros are a mess.

You can change "Having" to "WHERE".

Instead of using "like" use "=". "Like" is really only used if you are using wildcards such as; field1 like "APPLE%" which would return "APPLE", "APPLES" or "APPLESAUCE".

I'd recommend you use your own code, here is some sample code for treating a sheet as a database table.

In the below code you should not have to change the connection string as it builds the connection string off of the current workbook.

This looks for criteria in A1-A3 of sheet2 and pulls data into sheet3 that matches data in column A of Sheet1. Simply change the sheet names in the query string found just after ".Open".

Also include the reference "Microsoft Active Data Objects 2.6" or "Microsoft Active Data Objects 6.0" in your vba as seen below.



Here is the simple macro:


I hope this helps.
James

This isn't the issue, but you don't actually need the " & Chr(13) & " " & Chr(10) & " in your code, as long as you put a space between the two pieces it will work.  Here, I've split the text out, it has some obvious issues:

    "SELECT
 table_name.field1_name,
 table_name.field2_name,
 table_name.field3_name,
 table_name.field4_name,
 table_name.field5_name,
 table_name.field6_name,
 table_name.field7_name,
 table_name.field8_name,
 table_name.field9_name,
 table_name.field10_name,
 table_namefield11_name,
 table_name.field12_name,
 table_namefield13_name,
 table_namefield14_name,
 table_namefield15_name,
 table_namefield16_name "
    "FROM db_firmprod.dbo.table_name "
    "GROUP BY
 table_name.field1_name,
 table_name.field2_name,
 table_name.field3_name,
 table_name.field4_name,
 table_name.field5_name,
 table_name.field6_name,
 table_name.field7_name,
 table_name.field8_name,
 table_name.field9_name,
 table_name.field10_name,
 table_namefield11_name,
 table_name.field12_name,
 table_namefield13_name,
 table_namefield14_name,
 table_namefield15_name,
 table_namefield16_name " 
    "HAVING
   (table_name.field1_name Like 'APPLE'
 Or table_name.field1_name Like 'BANANA'
 Or table_name.field1_name Like 'GRAPE')
 AND (table_name.field14={ts '2026-01-31 00:00:00'})
 AND (table_name.field15_name={ts '2026-02-14 00:00:00'})" )

One of the things I like to do, which is a bit of a pain I will admit, when building SQL statements is to put either individual fields or blocks on a single line, it makes them easier to read:

strSQL =    "SELECT " _
 & "table_name.field1_name, " _
 & "table_name.field2_name, " _
 & "table_name.field3_name, " _
 & "table_name.field4_name, " _
 & "table_name.field5_name, " _
 & "table_name.field6_name, " _
 & "table_name.field7_name, " _
 & "table_name.field8_name, " _
 & "table_name.field9_name, " _
 & "table_name.field10_name, " _
 & "table_name.field11_name, " _
 & "table_name.field12_name, " _
 & "table_name.field13_name, " _
 & "table_name.field14_name, " _
 & "table_name.field15_name, " _
 & "table_name.field16_name " _
    & "FROM db_firmprod.dbo.table_name " _
    & "GROUP BY" _
 & "table_name.field1_name, " _
 & "table_name.field2_name, " _
 & "table_name.field3_name, " _
 & "table_name.field4_name, " _
 & "table_name.field5_name, " _
 & "table_name.field6_name, " _
 & "table_name.field7_name, " _
 & "table_name.field8_name, " _
 & "table_name.field9_name, " _
 & "table_name.field10_name, " _
 & "table_name.field11_name, " _
 & "table_name.field12_name, " _
 & "table_name.field13_name, " _
 & "table_name.field14_name, " _
 & "table_name.field15_name, " _
 & "table_name.field16_name "  _
    & "HAVING "
 &   "(table_name.field1_name Like 'APPLE' " _
 & "Or table_name.field1_name Like 'BANANA' " _
 & "Or table_name.field1_name Like 'GRAPE') " _
 & "AND (table_name.field14_name={ts '2026-01-31 00:00:00'}) " _
 & "AND (table_name.field15_name={ts '2026-02-14 00:00:00'})"

Then have the line read something like:

 .CommandText = Array( strsql )

James has some good ideas as well, all I've done here is clean up a bit of SQL.  If you have time, I would recommend studying his post to understand what he is proposing.

Thanks for the input guys.

James, I have a couple of follow up questions, as this code is new to me.

I tried running the code, and got the following error:  "Run-time error '-2147517900 *80040e14)':   Syntax error (missiong operator) in query expression 'BA='APPLE' Or 'BANANA' Or 'GRAPE' AND BA='1/31/2011' AND BA='2/14/2011'".

What I'm trying to do:
What I'm querying for queryitem1 could be one item (ex. APPLE), or 100+ items.  If cell A1=APPLE, A2=BANANA, and A3=GRAPE, I have formulas set up so that the value of cell BA5  equals " 'APPLE' Or 'BANANA' Or 'GRAPE' " (encompassing all inputs from column A).  In VBA, I then have queryitem1 set equal to Range("BA5").Value.

Since I'm trying to pull 1/31/2011 data that was sent on 2/14/2011 for APPLE, BANANA, GRAPE, etc, i have cell BA6=1/31/2011 and cell BA2 equal to 2/14/2011.  These dates should also be dynamic, as they could change as well.  I have queryitem2 set to BA6 and queryitem3 set to BA2.  (I excluded the dates from my initial question yesterday, as I was trying to at least get the other data to pull keeping the dates static first, but I don't know how to do that in this code).

To incorporate into the code you sent, I used:
.Open "INSERT INTO [DataResults$] SELECT * FROM [DataResults$] WHERE BA ='" & queryitem1 & "' AND BA = '" & queryitem2 & "' AND BA = '" & queryitem3 & "' "

A couple of other things I'm not sure how to do:

The connection string I have for the data I'm trying to pull is:  DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod
I'm not sure how to incorporate this into your code.

Also, how can I specify which cell I want the results displayed?

Am I way over my head in this?  I thought I was so close yesterday.

Thanks again for all of your help!!

Q. I tried running the code, and got the following error:  "Run-time error '-2147517900 *80040e14)':   Syntax error (missiong operator) in query expression 'BA='APPLE' Or 'BANANA' Or 'GRAPE' AND BA='1/31/2011' AND BA='2/14/2011'".

A. You got the error because you aren't specifying the column on each statement. There are two ways you can do this.

Your syntax for 'BA='APPLE' Or 'BANANA' Or 'GRAPE' AND BA='1/31/2011' AND BA='2/14/2011' is basically stating ,"Find column BA where it is either APPLE <ERROR> AND 1/31/2011 AND 2/14/2011.

A column can't be 'APPLE' and '1/31/2011' and '2/14/2011. That's like saying to a basketball player he can start the next game if he scored exactly 14 points in the last game, and exactly 18 points in the last game and exactly 20 points in the last game. You can only have one absolute.

Just an FYI, keep in mind that "BA" is the 78th column in your spreadsheet as your columns go go A-Z, AA-AZ, BA-BZ, ect. Is BA correct (that's a big spreadsheet).

WHERE BA ='APPLE' OR BA='BANANA' OR BA='GRAPE' AND (BA='1/31/2011' OR BA='2/14/2011')

So that you don't have to specify the column name in each instance you can use an array and use the operator "in".

WHERE BA in ('APPLE','BANANA','GRAPE') AND (BA='1/31/2011' OR BA='2/14/2011')

Lets say column BB is your date column and you want to find all the Apples,Bananas and grapes in BA between a date range in BB.

WHERE BA in ('APPLE','BANANA','GRAPE') AND (BB Between '1/31/2011' AND '2/14/2011')

"Since I'm trying to pull 1/31/2011 data that was sent on 2/14/2011 for APPLE, BANANA, GRAPE, etc, i have cell BA6=1/31/2011 and cell BA2 equal to 2/14/2011.  These dates should also be dynamic, as they could change as well.  I have queryitem2 set to BA6 and queryitem3 set to BA2.  (I excluded the dates from my initial question yesterday, as I was trying to at least get the other data to pull keeping the dates static first, but I don't know how to do that in this code)." - Mike M.

Keep in mind that your interface options (APPLE,BANANA,GRAPE, 1/31/2011 and 2/14/2011) are on a separate sheet and are pulled in the macro by:

To incorporate into the code you sent, I used:
.Open "INSERT INTO [DataResults$] SELECT * FROM [DataResults$] WHERE BA ='" & queryitem1 & "' AND BA = '" & queryitem2 & "' AND BA = '" & queryitem3 & "' "

The issue with your statement is that you are inserting into your dataresults sheet from your dataresults sheet.

If the sheet that is your master data sheet is named "SalesData" (holds all the information) you would want...

.Open "INSERT INTO [DataResults$] SELECT * FROM [SalesData$] WHERE BA ='" & queryitem1 & "' AND BA = '" & queryitem2 & "' AND BA = '" & queryitem3 & "' "

Also BA is a single column (76th column in excel) and you are once again asking where BA='Apple' AND BA='BANANA' AND BA='Grape'. The column BA can't be all at once, these should be OR statements.

.Open "INSERT INTO [DataResults$] SELECT * FROM [SalesData$] WHERE BA ='" & queryitem1 & "' OR BA = '" & queryitem2 & "' OR BA = '" & queryitem3 & "' "

Q.The connection string I have for the data I'm trying to pull is:  DSN=firm_prod;NA=firmprod1,6100;DB=db_firmprod. I'm not sure how to incorporate this into your code.

A. You don't have to.

The following code:

wb = ThisWorkbook.FullName (this resolves to C:\Users\MikeM\Desktop\myworkbook.xls for example)

THEN it does this:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;"
This creates the connection string based off the workbook that is running the macro. It sets the data source to the path name of the workbook. There is no editting of the connection string. Had I set this to a static you would have to specify where the workbook and the workbook name and if you moved the workbook to another directory the macro would fail. This is not the case by having the macro get the workbooks path and name.

Q. Also, how can I specify which cell I want the results displayed?

A. You can change:
strConn = strConn & "Extended Properties=""Excel 8.0;HDR=NO
to
strConn = strConn & "Extended Properties=""Excel 8.0;HDR=YES

The only thing about doing this is...

The first row in your sheets is treated as the column name and queries will then use the first row as the column name instead of "A", "B", "C".

I chose to give you the code with HDR=NO because you had so many columns you were copying and the sheet that will be inserted to has to have the same column names when you use "INSERT INTO [dataresults$] SELECT * FROM".

If you want, include your column names in your reply and I'll show you how to setup up your queries that way.

Let me know how it goes, your not that far off from what your looking to get.



My apologies for the delay -- I have been a bit tied up with work.

I am still struggling with this issue.  I feel like I am missing something.

Let me take a step back.  Below is somewhat of a mock of what I am trying to put together:


The different types of fruit listed in column B can range from 1 to 10 to 100.  In cell H1 is a formula that pulls the list from column B into one cell, and consists of the whole range.  Cells B3 and E1 contain the dates needed to pull the necessary data.  The user should be able to list the fruit needed, run the macro, and the table in cell E5 will populate.  Cells H1, B3, and E1 are all variable entries.

The way this mock example is set up has everything on one sheet.  If you think it is easier to have the data inputs listed on a separate sheet from the table, I can make that change.

So, if I set the following: