Solved: Get value from Select query

[mdmackillop]

The following should produce a single result. How do I get it to enter the Value into my worksheet?
It's been a long day!!!


Sub GetAddr()
Dim oConn As Object
Dim sSQL As String
Dim Sql As String
Dim Addr As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\Database\ValData2000.mdb"
sSQL = "SELECT ValPropDet.Record_Number, [NameNo] & ' ' & [Street] & ' ' & [town] AS Addr " & _
"FROM ValPropDet " & _
"WHERE (((ValPropDet.Record_Number)=" & [B26] & "));"

oConn.Execute sSQL

oConn.Close
Set oConn = Nothing

End Sub

[CreganTur]

I think you need to create a recordset object. Try:

Sub GetAddr()
Dim oConn As Object
Dim sSQL As String
Dim Sql As String
Dim Addr As String
Dim rst As Object

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\Database\ValData2000.mdb"
sSQL = "SELECT ValPropDet.Record_Number, [NameNo] & ' ' & [Street] & ' ' & [town] AS Addr " & _
"FROM ValPropDet " & _
"WHERE (((ValPropDet.Record_Number)=" & [B26] & "));"
Set rst = CreateObject("ADODB.Recordset")
rst.open sSQL, oConn

'return values
Debug.Print rst.Fields("Record_Number").Value
Debug.Print rst.Fields("Addr").Value

rst.Close
oConn.Close
Set rst = Nothing
Set oConn = Nothing

End Sub


HTH:thumb

[mdmackillop]

Thanks Randy,
Just what I needed.
Regards
Malcolm