Robert_L_Ross said:
We are looking for a way to put an array into a query to use the NPV
function. Does anyone have any experience in this area?
NPV(rate,array)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It would probably be a better idea to have a user-defined function in
the query that then uses the NPV() function. E.g.:
SELECT GetNPV(MyID) As NPV_Value
FROM ...
WHERE ...
The function (just an example):
Public Function GetNPV(lngID as Long)
Const SQL = "SELECT [rate], [value] FROM someTable WHERE ID = "
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dblRate As Double
Dim dblValues() As Double
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset(SQL & lngID)
' Get the [values] that will make up the array elements
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
ReDim dblValues(rs.Recordcount)
i = 0
dblRate = rs![rate]
Do While Not rs.Eof
dblValues(i) = rs![value]
i = i + 1
rs.MoveNext
Loop
' Run the NPV function
GetNPV = NPV(dblRate, dblValues())
End If
End Function
Add error traps as required.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQvpWtIechKqOuFEgEQLdtwCffEq/ObYLB+wDcif55gKjxv6i/yMAnjqz
fvuJlH+YyE2BOfQ50FXOo2iG
=Lsa3
-----END PGP SIGNATURE-----
