Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple parameter database query
Hi I've been struggling with this for a week or so.
What I'm trying to do is make a query that takes a large and variable range of cells and uses them as parameters for a SQL query. Does anyone any experience with this? I can do a parameter query from one cell, but I haven't been able to expand the query. Thanks, Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple parameter database query
Let me give a little more details.
The parameter cells are on one sheet, and the DB output is on another. The Microsoft Query is SELECT * FROM accounts WHERE (acct_no=?) And the source parameter cell is A1. What i want to do is have a range (of unspecified length) of inputs in column A, and have the query dynamically expand to WHERE (acct_no=?) OR (acct_no=?) OR (acct_no=?)..... I know how to name a dynamic range of cells using OFFSET. I have no experience using VBA, but it sounds like it's the only way to accomplish this. Thanks for your help, Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple parameter database query
Paul,
This is untested but it would go something like this: Dim strSQL as string Dim lRow as long strSQL = "SELECT * FROM accounts WHERE acct_no IN (" lRow = 1 With Sheets("MySheetName") Do while not isempty(.cells(lrow,1)) strsql = strSQL & .cells(lrow,1).value & "," lrow = lrow +1 loop end with strsql = left(strsql,len(strsql)-1) & ")" HTH, Robin Hammond www.enhanceddatasystems.com wrote in message oups.com... Let me give a little more details. The parameter cells are on one sheet, and the DB output is on another. The Microsoft Query is SELECT * FROM accounts WHERE (acct_no=?) And the source parameter cell is A1. What i want to do is have a range (of unspecified length) of inputs in column A, and have the query dynamically expand to WHERE (acct_no=?) OR (acct_no=?) OR (acct_no=?)..... I know how to name a dynamic range of cells using OFFSET. I have no experience using VBA, but it sounds like it's the only way to accomplish this. Thanks for your help, Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple parameter database query
I don't belive MS Query can use dynamic range names. Your alternatives are to
resize a range-referencing name whenever the criteria changes or define the name to include as many blank cells as you think you might need in the future. You could also drive the query via VBA, but if the only reason to do that is to accomodate the range it might not be worth the trouble. Does that help? *********** Regards, Ron XL2002, WinXP-Pro " wrote: Let me give a little more details. The parameter cells are on one sheet, and the DB output is on another. The Microsoft Query is SELECT * FROM accounts WHERE (acct_no=?) And the source parameter cell is A1. What i want to do is have a range (of unspecified length) of inputs in column A, and have the query dynamically expand to WHERE (acct_no=?) OR (acct_no=?) OR (acct_no=?)..... I know how to name a dynamic range of cells using OFFSET. I have no experience using VBA, but it sounds like it's the only way to accomplish this. Thanks for your help, Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple parameter database query
Thanks everyone.
Basically I used Robin's VB except my acct_no is a string, so I used single quotes. Sub Multiquery1() Dim strSQL As String Dim lRow As Long strSQL = "SELECT * FROM ACCOUNTS WHERE (ACCT_NO IN ('" lRow = 1 With Sheets("Trade #") Do While Not IsEmpty(.Cells(lRow, 1)) 'Assuming my values are in A1 strSQL = strSQL & .Cells(lRow, 1).Value & "','" ' That's singles quotes in there lRow = lRow + 1 Loop End With strSQL = Left(strSQL, Len(strSQL) - 2) & "))" 'Removes the last ,' Sheets("Display").Select Range("A1").Select With ActiveSheet.QueryTables(1) .CommandType = xlCmdSql .CommandText = strSQL ... End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parameter Query to return multiple values | Excel Discussion (Misc queries) | |||
parameter query - multiple options | Excel Discussion (Misc queries) | |||
Parameter Query-enter just once for use in multiple places | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
automate query parameter refresh and multiple chart print out | Excel Programming |