An array element is just like a variable, so you will have to do this:

..Open "SELECT DISTINCT PRICING.PRICE FROM MAIN, PRICING WHERE
(MAIN.SERIES LIKE 'A123') AND (PRICING.TYPE = 'D') AND (MAIN.ID =
PRICING.PART_ID) AND (PRICING.START = " & thisarray(i,1), dbConn2,
adOpenStatic

Otherwise thisArray(i,1) will taken as that literal string, which obviously
means nothing.

RBS


"mazzarin" wrote in message
ups.com...
Hi,

I'm trying to read data from a worksheet, inputting it into an array,
and make SQL queries based on the array value, repeat for each value.
I'm having problems getting the array value into the query though.

Heres example code:


Sub Pass_array()
Dim myarray As Variant
myarray = Range("C15:C18").Value
receive_array myarray

End Sub

---

Sub receive_array(thisarray)
Dim dbConn2 As New ADODB.Connection
Dim rs2 As New ADODB.Recordset 'the recordset

dbConn2.ConnectionString = (connection string here, works fine)

dbConn2.Open



For i = 1 To UBound(thisarray)

MsgBox thisarray(i, 1) *** <-- this is my check to see what value is
getting put in

With rs2

.Open "SELECT DISTINCT PRICING.PRICE FROM MAIN, PRICING WHERE
(MAIN.SERIES LIKE 'A123') AND (PRICING.TYPE = 'D') AND (MAIN.ID =
PRICING.PART_ID) AND (PRICING.START=thisarray(i,1)", dbConn2,
adOpenStatic

noRecords = .RecordCount

Range("F15").CopyFromRecordset rs

.Close

End With

Next

dbConn.Close


End Sub


If I do this, it says thisarray is not a function. If I just input i,
it says invalid column name.

Any help is appreciated thanks.