Multiple Parameters in External Data Queries

Laura asks:

I am trying to use one query but with multiple paremeters depending on the cell. For example I have a list of dates in Column A and I want to run the query for each dates shown in column B, so the paramter for B1 is A1, B2 is A2 etc.. How do I do this in Excel?

I suppose you could have a bunch of different queries – one for each cell. I think that would be hard to maintain and probably lead to a huge Excel file. I would use a helper sheet that stored one query and use formula to bring over the data I need.

First, create a query on a sheet that contains all the records for the table you’re interested in. For this example, I’m using the Invoices table from Northwind.mdb (actually mine is called Nwind.mdb, but I don’t know what the difference is). Place the query in A3, or if you already have it in A1, insert a couple of rows above the query. We’ll need them later. If you need help creating this query, see Querying External Data in Excel.

Next, create a new sheet and put your dates in column A. Back on the query sheet, put some formulas in B1 and B2.

B1: =TEXT(MIN(Summary!A2:A9),”mm/dd/yyyy”)
B2: =TEXT(MAX(Summary!A2:A9),”mm/dd/yyyy”)

These will serve as the parameters for this query. It will limit the rows that are brought in, but there will still be more rows than you need. I needed the TEXT function because the ODBC driver couldn’t understand Excel’s dates.

Now I’ll make the query a parameter query. I do this in VBA, but you can use Query Manager, Query Editor, or you can edit the SQL statement directly in MSQuery. In the Immediate Window of the VBE, I type ?activecell.QueryTable.CommandText (with a cell in the query selected, or course), and see that the SQL statement is:

SELECT Invoices.ShippedDate, Invoices.CustomerID, Invoices.ExtendedPrice FROM [Invoices]

To add the parameters, I type this into the Immediate Window:

activecell.QueryTable.CommandText = activecell.QueryTable.CommandText & ” WHERE (Invoices.ShippedDate >= ? and Invoices.ShippedDate < = ?);”

In the end, the SQL statement should be:

SELECT Invoices.ShippedDate, Invoices.CustomerID, Invoices.ExtendedPrice FROM [Invoices] WHERE (Invoices.ShippedDate >= ? and Invoices.ShippedDate < = ?);

Now when I refresh the query table, Excel will prompt me for parameter values. Just type something that looks like a date and don’t worry about what it returns. If all has gone well, the Parameters button on the external data toolbar will now be enabled. Click it to open the Parameters dialog.

I change Parameter1 to point to cell B1 and Parameter2 to point to cell B2. Now the query is set to return a limited number of rows, but it will contain all the rows you need.

The last step is make the formula on the non-query sheet. With dates in column A, I put this formula in column B to return the first CustomerID for that date.

=VLOOKUP(A2,Data!Query_from_MS_Access_Database,2,FALSE)

and I get this:

Posted in Uncategorized

14 thoughts on “Multiple Parameters in External Data Queries

  1. Dick,

    I think the challenge is to get a Range of Cells as input Parameters

    Eg. A1:A10 contain Customer ID 1001,2004,1959,3489 etc
    How do you get the value from Cells rather than Cell

    The only way I think it is possible is through code..of course you cant have a huge range of cells…beyond certain point you get “Query too complex” error

    Option Explicit

    Sub QSC_PARM()
    ‘Queries the Customer Master
    Dim Ar As String, Ar1 As String, Ar2 As String, Ar3 As String
    Dim SorSht As String, QryNm As String
    Dim DestSht As Worksheet
    Dim i As Long

    SorSht = “Sheet1$”
    Set DestSht = Sheet2
    QryNm = “QRY_PARM”
    Ar1 = “ODBC;DSN=Excel Files;DBQ=” & Range(“VBA_FPN”).Value & “;DefaultDir=” & Range(“VBA_FP”).Value & “;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”
    ‘ VBA_FPN and VBA_FP – Names for File Paths
    Ar = “SELECT *”
    Ar2 = Ar & “” & Chr(10) & “FROM ‘” & Range(“VBA_FPN1?).Value & “‘.'” & SorSht & “‘ ‘” & SorSht & “‘” & Chr(10) & Chr(13) & “WHERE (‘” & SorSht & “‘.ID=” & Range(“ID”)(1).Value & “)”

    For i = 2 To Range(“ID”).Count
    Ar2 = Ar2 & ” OR (‘” & SorSht & “‘.ID=” & Range(“ID”)(i).Value & “)”
    Next i

    With DestSht.QueryTables(QryNm)
    Ar3 = Replace(Ar1, “ODBC”, “OLEDB”, 1, 1)
    .Connection = Ar3
    .CommandText = Ar2
    .Connection = Ar1
    .CommandText = Ar2
    End With
    DestSht.QueryTables(QryNm).Refresh BackgroundQuery:=False
    End Sub

  2. Sam,

    Public Function InListSqlString(rngFirstGroup As Range) As String
    Application.Volatile (True)

    Dim strGroupNumberString As String
    Dim rngGroupsInList As Range, rngCurrentID As Range
    Dim intGroupCount As Integer, intCounter As Integer

     intCounter = 0

    If rngFirstGroup.Value = “” Then
      InListSqlString = “”
      Exit Function
    End If

    If Not (IsEmpty(rngFirstGroup.Offset(1, 0))) Then
       Set rngGroupsInList = Range(rngFirstGroup, rngFirstGroup.End(xlDown))
         intGroupCount = rngGroupsInList.Rows.Count
       For Each rngCurrentID In rngGroupsInList
          strGroupNumberString = strGroupNumberString &amp; “‘” &amp; rngCurrentID.Value &amp; “‘”
          intCounter = intCounter + 1
            If intGroupCount  intCounter Then strGroupNumberString = strGroupNumberString &amp; “, “
       Next rngCurrentID
       
       InListSqlString = strGroupNumberString
       
     Else
       InListSqlString = “‘” &amp; rngFirstGroup.Value &amp; “‘”

    End If

    End Function

    I use this to generate a string from a vertical range which are user inputs. I’ve got notes to the users about the need fot the range to be contiguous. I insert it in the SQL string I build as the argument for an “In” in the WHERE clause. It’s not elegant, I’m sure, but it works well.

    Brett

  3. Dick, Brett..
    Thanks for those suggestions.

    I just modified the code…changed WHERE/OR to WHERE IN and used a String to Array Function….ran a query of about 5000 Customer ID’s typed in a range of cells… works great… thanks for this post..

    Option Explicit
     
    Sub QSC_PARM()
    ‘Queries the Customer Master
    Dim Ar As String, Ar1 As String, Ar2 As String, Ar3 As String
    Dim SorSht As String, QryNm As String
    Dim DestSht As Worksheet
    Dim i As Long
     
    SorSht = “Sheet1$”
    Set DestSht = Sheet2
     QryNm = “QRY_PARM”
    ReDim RngArr(1 To Range(“ID”).Count)
     
    Ar1 = “ODBC;DSN=Excel Files;DBQ=” &amp; Range(“VBA_FPN”).Value &amp; “;DefaultDir=” &amp; Range(“VBA_FP”).Value &amp; “;DriverId=790;MaxBufferSize=2048;PageTimeout=5;”
    Ar = “SELECT *”
    Ar2 = Ar &amp; “” &amp; Chr(10) &amp; “FROM ‘” &amp; Range(“VBA_FPN1”).Value &amp; “‘.'” &amp; SorSht &amp; “‘ ‘” &amp; SorSht &amp; “‘” &amp; Chr(10) &amp; Chr(13) &amp; “WHERE ‘” &amp; SorSht &amp; “‘.ID IN (“
    Ar3 = Range(“ID”)(1).Value
     
    For i = 2 To Range(“ID”).Count
    Ar3 = Ar3 &amp; “, “ &amp; Range(“ID”)(i).Value
    Next i
     
    Ar2 = Ar2 &amp; Ar3 &amp; “)”
     
    With DestSht.QueryTables(QryNm)
    Ar3 = Replace(Ar1, “ODBC”, “OLEDB”, 1, 1)
    .Connection = Ar3
    .CommandText = StringToArray(Ar2)
    .Connection = Ar1
    .CommandText = StringToArray(Ar2)
    End With
    DestSht.QueryTables(QryNm).Refresh BackgroundQuery:=False
    End Sub
     
    Function StringToArray(Query As Variant) As Variant
     
       Const StrLen = 127
       Dim NumElems As Integer
       Dim Temp() As String
       Dim i As Long
       NumElems = (Len(Query) / StrLen) + 1
       ReDim Temp(1 To NumElems) As String
       For i = 1 To NumElems
          Temp(i) = Mid(Query, ((i – 1) * StrLen) + 1, StrLen)
       Next i
       StringToArray = Temp
    End Function
  4. Thanks for the concise info on how to get parameters from cells into table queries of external data in Excel. I lucked out, your post came up first on Google!

  5. There is a trick for doing a SQL query for a fixed range of products/customers from inside Excel without programming:

    I will show this for a sql server query. I will omit the standard steps from this post, but i will show the specifics.

    Step 1:

    Use a fixed range/list to put all customer id’s. For e.g. A1-A10:
    CUST1
    CUST2
    CUST3
    ….
    CUST10

    Step2

    Create a formula in adjectant cell B1:
    =”|”& A1 & “|” (equals “|CUST1|”)
    Create a formula in B2
    =B1 & A2 & “|” (equals “|CUST1|CUST2|”)

    Fill the formula in B2 down for all the customers (till A10)

    Create a Query (maybe on another sheet):

    SELECT * FROM CUSTOMERS WHERE ? LIKE ‘%|’ + CUSTOMER_ID + ‘|%’

    Refresh the query and select as the parameter the cell A10

    You should only see a list of customers that you entered in A1-A10.

    Note the following

    For SQL server the limit is 8000 characters long, which is can contain quite a number of customer id’s, but depending on the length of the customer id not hundreds or more (check the length of the text in A10).

    This trick also works even if not all customer ids are filled.

    Also, this is not a super fast query.

    Several variations on this theme can be used with different database platforms and range of parameters.

  6. DM Unseen,
    I just tried your method…it gave me an error “Too few parameters expected 1? …. could you kindly post a file containing the query….

    sam

  7. First, this example is for SQL Server (and your error indicates Access), I have not studied to create this for Access, Although it is certainly possible.

    I’ll try to get something, but this will be on monday. I’m not sure how to upload it here btw.

  8. DM Unseen,
    Thanks in advance for your time….if it is not possible to post it here could you kindly e-mail it to sgbhide At gmail dot com

    sam


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *