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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item_number[/TD]
[TD]SQL CRITERIA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]('1234','4567','7890')[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4567[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]7890[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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