ODBC SQL string - type mismatch

[spike1988]

So I am creating a macro that reads anything from cell A4 until there is a blank row.

I concatenate every string column A that has data and turn it into a string. Then I inserted the string into the sql select statement and it works when I have like 6 cells selected. When I select all the cells I need then it crashes and give me the error message 'Run time error "13" type mismatch"

This is a small sample of cells, but at this point the macro is already crashing.
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
'12345678'
''
'12345678'


Thank you in advance for all your help!!!

Please Login or Register  to view this content.

[Norie]

What exactly is in the variable s when the code fails?

Do the cells actually have a leading and trailing single quote?

If they do then you might be losing the leading quote which would make what's in s incorrect.

Try adding the single quote.

Please Login or Register  to view this content.

[spike1988]

Variable s contains order numbers like these:
12345678
12345678

Variable S puts them into comma delimited and I added the single quotes in excel. I am not sure what the problem was, but I used this to fix it : http://www.mrexcel.com/forum/excel-q...lications.html

The fix was to turn the commandtext array into a string and then call the string. Please see code for reference:

Please Login or Register  to view this content.

[Norie]

Ah, that old thread.

Should have suggested using a string, it's what I do.