using a VB named range in SQL Query in VB

[aka_krakur]

I have some code going where the VB adds the SQL statement to query a database.
That part of the VB is working fine

I am attempting to add a defined range to the criteria of the SQL statement

so within the SQL Statement I have something like this:

Select ID, part_number, lot_number from table1
where part_number in (123456, 654321)


I would like to change the 123456, 654321 to a named range
So in the beginning of my VB I have the following:

PartRange = Sheet1.Range(Cells(10, 2), Cells(10, Sheet1.Range("iv10").End(xlToLeft).Column))

which that code defines the range of various part_numbers user may enter that they want to query.

I thought it was as simple as changing
where part_number in ( & PartRange & )
but I am getting a type mismatch debug error.

suggestions/solutions?

 

[Norie]

You'll need to convert the values in the named range to a comma separated list.

If they are all numeric then you might be able to use Application.Transpose and Join.

 

[aka_krakur]

Sorry, I should have stated that. I have converted them to comma separated.

So in Cell B9 & C9 the user has input 123456 & 654321 respectively

I have a formula that just converts these directly below in B10 & C10
B10 = '123456',
C10 = '654321'

So I somehow want an end result in my VB :

select *
from table1
where part_number in ('123456',654321') by having it state where part_number in (B10, c10)

of course all that dependent on how many part numbers users input

 

[aka_krakur]

Disregard, I understand what to do now.

 

[aka_krakur]

Actually I do still need help;

Now my problem is figuring out how to get my VB to concatenate based on how many values the user needs

So let's say for instance the user input 4 part_numbers

B10 = 123456
C10 = 654321
D10 = 111111
E10 = 222222

I would need these concatenated then as 123456, 654321, 111111, 222222 in let's say cell A10

So that my SQL can state part_number in (a10)

so that the query returns any data where it finds those 4 part numbers.

 

[Norie]

Sorry, didn't see your first example.

If you already have the delimited list you don't need the &.

So

In(PartRange)

should work.

If you had the list in a cell:

In(Range("A10").Value)

Have you tried that?

If you have and it doesn't work I think we might need to see the rest of the code.

 

[aka_krakur]

I am still getting a Run-time error '1004':
General ODBC error

Different error than before; so that is a good sign.

I haven't gotten as far as generating a concatenated list
but I do have the range called out in the VB as previously listed:


PartRange = Sheet1.Range(Cells(10, 2), Cells(10, Sheet1.Range("iv10").End(xlToLeft).Column))

then in my sql code I have part_number in (PartRange)

Using the Step Into {F8} functionality in the VB window
it makes it all the way down the code and fails on this line:

.Refresh BackgroundQuery:=False

which seems like it is not liking whatever the "value" is in the PartRange
Is there a way to show that value that my PartRange is

 

[Norie]

Can you post the rest of the code?

Are you creating the SQL as a string?

 

[aka_krakur]

Here is all I have so far.
Not much to it
If I type the breakdown of the part_numbers exactly, it works.
It's something to do with the PartList or the PartRange

The results from the PartRange are completely wrong. It results in only '123456',

The results of PartList show that it = '123456', '654321','111111','222222'
however, when I add a statement to the vb to show me the value of this in a cell it looks like this:

123456','654321', '111111','222222' missing the first asterisk in the front.
it is needed, unfortunately the format in the oracle database the sql is pulling from is needing the asterisks if it is an IN selection
I tried refreshing query manually with Part_number in (123456, 654321, 1111111, 2222222)
and got a similar error.
As soon as I made them look like Part_number in ( '123456', '654321','111111','222222') query runs successfully; problem is I don't know how to get the numbers like that with concatenation or VB first and foremost.

Sub QueryRefresh()
             ' Messaging this PartRange out as it is not working
             'PartRange = Sheet1.Range(Cells(10, 2), Cells(10, Sheet1.Range("iv10").End(xlToLeft).Column)).VALUE
PartList = Sheet1.Range("D10").VALUE ' This is where I have the concatenated values
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=DATABASE_NAME;UID=USER1;PWD=PASSWORD1;DBQ=DATABASE_NAME;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM" _
        ), Array("=IfAllSuccessful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), _
        Destination:=Sheet6.Range("A1"))
        .CommandText = Array( _
        "Select * From TABLE1 where a.part_number in (PartList)" _
        )
   .Name = "Query from DATABASE_NAME"
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[Norie]

Sorry again - I wasn't sure about how you were using PartList

Perhaps this?

"Select * From TABLE1 where a.part_number in (" & PartList & ")"

It might be an idea to use a string for the SQL so you can check it when you debug.

Something like this which 'builds' the SQL statement.

strSQL = "SELECT * FROM TABLE1 "
strSQL = strSQL & " WHERE a.part_number IN (" & PartList & ")"

You could then try using strSQL like this.

 .CommandText = Array(strSQL)

If you have access to the database to run queries you could even output the string to the Immediate Window (CTRL+G) with this,

Debug.Print strSQL

copy it, and try it in the database.