VBA to amend SQL Query

[boroyj]

Hi all,

I am trying to retrieve data from a SQL connection in an excel workbook. Using MS query and the parameters options I am able to parse a single item number and have the query bring back that relevant information from the database.

I would like it to bring back the information for multiple item numbers in one go, however, I cannot get that to happen.

I've tried pulling all of the numbers into the format that would work in a normal IN statement for example:

Ref
1234
4567
7890

is presented as ('1234',4568','7890') and pointed the parameter cell reference to that, but keep getting '[Microsoft][ODBC SQL Server Driver]String data, right Truncate' error.

Is there a VBA method that can take the multiple rows of item numbers and amend the query to look them up?

Thanks in advance

Rob

 

[Comfy]

Is this a copy and paste error??

('1234',4568','7890')

You're missing an apostrophe.

Otherwise please share your VBA for us to read.

 

[boroyj]

Is this a copy and paste error??

('1234',4568','7890')

You're missing an apostrophe.

Otherwise please share your VBA for us to read.

yes, it is missing the ' from where I typed it sorry.

I don't have any VBA to share, I was hoping that someone had the same issue sorry.

If it's unclear above - what I am essentially trying to do is take the item numbers which would be listed in one column, then format them in a cell or with VBA code so that:

	A	B
1	Item_number	SQL CRITERIA
2	1234	('1234','4567','7890')
3	4567
4	7890

<tbody>
</tbody>

Can be converted to a format that is usable in a SQL statement that is embedded already. for example the above is converted to '1234','4567' to be used in

Select *
from products
where item_number in B2

Thanks

Rob