Using .Fields() method in Recordsets for calculations. 1

[StevenFromSouth]

Hi all,
I was fiddling around with Recordsets in VBA and I noticed that you can do things like this

"SELECT TOP 1 [b]TotalCost/Amount[/b] FROM Sales WHERE [SalesDate] <= " & strDate & _
         " And [ProductID] = " & PID & _
                " ORDER BY [SalesDate] Desc;"

also

With CurrentDb.OpenRecordset(strLastUnitCost)
        If Not (.BOF And .EOF) Then
            fncLastUnitCost = [b].Fields(4) / .Fields(3)[/b] 'Field(4) is Total and Field(3) is Amount
        End If
    End With

Is it wise to pass those calculations through Recordsets??

 

[Andrzejek]

You can do your calculations in your code, or you can 'delegate' it to your DB:

Select ...,
Total / Amount As UnitCost, ...
From ...

And use (calculated) field UnitCost from your recordset

Instead of [tt].Fields(4) / .Fields(3) [/tt]I would rather [blue]do this[/blue] since the order of your fields could/may change if you modify your [tt]strLastUnitCost[/tt]

With CurrentDb.OpenRecordset(strLastUnitCost)
    If Not (.BOF And .EOF) Then
        fncLastUnitCost = [blue]!Total.value / !Amount.value[/blue]
    End If
End With

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson