Solved: referencing a record in a recordset

[lifeson]

Hi All

I use the following to get a record from an access database.
It will always return a single record
How do I get the vallue from the recordset wihout having to write it to a worksheet first?
The record will be from TblCostFactor.price

[vba]
Function Uplift(ptype As String, val As Double) As Double
Dim qry As String
Dim col As Long
qry = "SELECT TblCostFactor.FactorID, TblCostFactor.LowerRange, " & _
"TblCostFactor.UpperRange, TblCostFactor.Price, " & _
"TblCostFactor.PatchID, TblCostFactor.ProductID, TblCostFactor.ActiveFlag " & _
"FROM TblCostFactor " & _
"WHERE (((TblCostFactor.LowerRange)<val) " & _
"AND ((TblCostFactor.UpperRange)>val) " & _
"AND ((TblCostFactor.ProductID)='CHA' " & _
"Or (TblCostFactor.ProductID)='" & ptype & "'));"
Set recordSet = New ADODB.recordSet
With recordSet
'get records
.Open Source:=qry, ActiveConnection:=Connection
'Clear existing records
Uplift = .Fields(Price).Value ' I thought this would work but doesn't
End With
MsgBox Uplift
Set recordSet = Nothing
End With
End Function
[/vba]

[Jan Karel Pieterse]

Change to:
Uplift = .Fields("Price").Value

[lifeson]

Change to:
Uplift = .Fields("Price").Value

Thanks Jan

I was almost right