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?

 

[Fazza]

Some thoughts.

One approach. Have a header on the Excel range so that the header & list can become a table. Then the SQL can be like

SELECT T.ID, T.part_number, T.lot_number
FROM table1 T, newtable N
WHERE T.part_number = N.part_number

Or some variant (of the SQL).

For the method you're currently using, consider loading the worksheet range of values into an array. Then in VBA using the JOIN function to create the string with all the separators. I got a little lost trying to code it simply! Still, this is the idea,

Dim i As Long
Dim arA As Variant
Dim arB As Variant
 
With Range("B2:B40")
  ReDim arA(1 To .Rows.Count, 1 To .Columns.Count)
  arA = .Value
  ReDim arB(LBound(arA, 1) To UBound(arA, 1))
  For i = LBound(arB) To UBound(arB)
    arB(i) = arA(i, 1)
  Next i
End With
 
Debug.Print "WHERE part_number IN ('" & Join$(arB, "', '") & "')"

 

[aka_krakur]

Thank you, that last portion worked.
I used

WHERE a.part_number IN (" & PartList & ")"

and it worked.

Now I don't know if I should post in a new thread; but I need some vb code assistance with either a formula or more thorough vb script for concatenating every cell depending on how many the user chooses

So if user only selects one, it needs result in cell A10 = 'Part_Number1'
If user selects 2 then it should result in A10 = 'Part_Number1', 'Part_Number2'
and so on for 3, 4, 5, 6 or more parts selected.

any ideas?

 

[Fazza]

Suggest you read my post above yours: it answers the question.

Same thing slightly differently,

Function CreateInText(rng As Range, AllEntriesNumeric As Boolean) As String
 
  Dim i As Long
  Dim ar() As String
 
  ReDim ar(1 To rng.Cells.Count)
  For i = LBound(ar) To UBound(ar)
    ar(i) = rng.Cells(i).Value
  Next i
 
  If AllEntriesNumeric Then
    CreateInText = "IN (" & Join$(ar, ", ") & ")"
  Else
    CreateInText = "IN ('" & Join$(ar, "', '") & "')"
  End If
 
End Function
 
Sub test()
Debug.Print CreateInText(rng:=Sheet1.Range("A12:A15"), AllEntriesNumeric:=True)
Debug.Print CreateInText(rng:=Sheet1.Range("A12:A15"), AllEntriesNumeric:=False)
End Sub