Depending on if you expect one or more rows your would do:

dim vVar

vVar = rst.GetRows

or

vVar = rst.Fields(0)


RBS


"andy" wrote in message
...
hi hope someone can help
i can connect to oracle through excel no problem
the problem i am having is i want to put the result of the following query
into a varable
at the moment i return the result into a cell on a excel worksheet then
use the value in the cell for a second query
here is the query i use

Sub form_a()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sql As String
Dim col As Integer
Dim data_to_find As Variant
Dim cttab As String

cnn.Open "Driver={microsoft odbc for oracle};" & _
"Server=EASY ;" & _
"Uid=easy;" & _
"Pwd=easy;"




sql = " select distinct code"
sql = sql & " from history"
sql = sql & " where trunc(date_time) = trunc(sysdate-1)"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly
Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
cttab = "CT_" & Cells(1, 1).Value

rst.Close

sql = " select brand from " & cttab & " "
sql = sql & " where trunc(calldate) = trunc(sysdate-1)"
sql = sql & " and brand is not null"
rst.Open sql, cnn, adOpenKeyset, adLockReadOnly


Cells(1, 1).Select
ActiveCell.CopyFromRecordset rst
rst.Close


End Sub