Is there way to grab a series of numeric values in a column, put them in a single cell and format them so that they are separated by commas and formatted as character data. We would like to do a search on numeric values as characters in a query. What is the maximum amount of values you can use in a select clause? Any ideas? I am so glad that forums exist!! Thanks in advance!
McrsftQry: Query off of text file
-
-
-
Quote from alreynolds2003
What is the maximum amount of values you can use in a select clause? Any ideas? I am so glad that forums exist!! Thanks in advance!
Technically there is no limit, although you will pay a price relative to the speed of the query the more parameters you introduce to the query..
As for what you are trying to do, I am not quite sure I understand.... would you care to elaborate ?
-
Additional Info
We have a series of account numbers in a column in excel. The list of accounts changes weekly and there are numerous accounts. Instead of typing each account number manually, we would like to create a query to search for information on these accounts. So we would like to find a way in excel to grab the account numbers in all the rows for the column and separate them by a apostrophe, comma apostrophe (formatting the accounts in character format). and then grab that value and pull it into a query as part of the select clause.
-
Sorry for not getting back to you earlier... lost sight of the thread...
OK, I have assumed your values are to be typed in column A of sheet1, starting with cell A2 and continuing until the last used row OK.
Here's how I'd build the SQL for the IN part
Code
Display MoreDim strParams As String Dim rngParams As Range, rngCell As Range strParams = "(" With Sheet1 Set rngParams = .Range("A2:A" & _ .Range("A65536").End(xlUp).Row) For Each rngCell In rngParams.Cells strParams = strParams & "'" & rngCell.Value & "'," Next rngCell strParams = Left(strParams, Len(strParams) - 1) strParams = strParams & ")" End With
This will produce a string variable such as this
('46017050','46017060','46017070')
Now you can use this in your SQL query like so
Tested this on a SQL db of mine & it worked fine. Hope it helps you
-
By the way, MS Query would not be able to handle this... so you'd need to use ADO... see the Advanced XL Integration section for examples of how this is done, or post back here with any questions.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!