Multiple parameter database query

[paul.salama]

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

 

[paul.salama]

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

 

[Robin Hammond]

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

 

[Guest]

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

 

[paul.salama]

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