Macro displays incorrect data when using IN statement in VBA code

[amolmacro]

I've written a macro that takes cell value from excel match it with the sql server table and return the data on the same spreadsheet. I think I'm doing it wrong. I'm not able get accurate data using below piece of code. Instead of using SQL IN statement in my query, I want to match single cell at one time using below SQL statement:

Please Login or Register  to view this content.

Here is the entire code for macro:

Please Login or Register  to view this content.

[millz]

It's probably not because it's not working, but used wrongly. The 'IN' condition part should be placed after 'WHERE' clause, not under the conditions for 'LEFT JOIN'.

[tony h]

Hi,
I noticed this post has had a lot of views without gaining a response.

it would help if you stated what the nature of the inaccuracy is.
Also to give an example of the contents of the rs on completion of the exec.

Also I am not sure what you mean by " Instead of using SQL IN statement in my query, I want to match single cell at one time using below SQL statement:"

[amolmacro]

millz- You're right. Can you give me a hand on this?

Tony h- I appreciate your response. In my below condition the s.[ShipTracking] can be same for multiple excel rows. In that situation IN statement doesn't work. Any help is appreciated. Thanks.

Please Login or Register  to view this content.

[millz]

I assume the SELECT portion is working fine.
Moved the 'IN' segment to 'WHERE' segment.

Please Login or Register  to view this content.

[amolmacro]

I tried modifying my sql query as you suggested. I'm not getting the desired output. Is there a way to check each record at a time from the spreadsheet. I'm sorry if I'm asking too many questions. I'm new to VBA and stuck with this issue from past 4 days. Thanks for your help.

[JosephP]

what are the values of newrow2 and newrow3?

[amolmacro]

Hi Joseph,

I've attached the screenshot of my excel spreadsheet. newrow2 has column F values and newrow3 has concatenated values from columns D,C, B, a from the spreadsheet.

Here is the code from newrow2 and newrow3:

Please Login or Register  to view this content.

Attachment 455708

[JosephP]

I can't open your attachment so can you explain exactly what is wrong with what happens when you run your code now?

[amolmacro]

The newrow3 can have same column values. In that situation my macro IN statement doesn't work correctly. Hope that helps. Here is the example:

3/23/2016 - 507 - CPT - Group C
3/23/2016 - 507 - CPT - Group C
3/23/2016 - 5051 - Flats - Group F

[amolmacro]

The newrow3 can have same column values. In that situation my macro IN statement doesn't work correctly. Hope that helps. Here is the example:

3/23/2016 - 507 - CPT - Group C
3/23/2016 - 507 - CPT - Group C
3/23/2016 - 5051 - Flats - Group F

[JosephP]

you can get a unique list like this

Please Login or Register  to view this content.