Using DAvg function

[GraeagleBill]

I might not be able to do this because of the "DATE" data-type of DOE, but I thought I'd post the question anyway. As one can see, I want to get the average value of the CLng of the "DOE" fields in the table "Ledgers". "AvgCriteria" seems to be formatting okay, as observed in Debug. The function is referenced as the RecordSource of an unbound text box. e.g., =PurchaseDate([InvstID])



Is the expression "CLng([DOE])" valid as coded?
BTW, TType is an autonumber field, so it's data type long.

Private Function PurchaseDate(ID As Long) As Date
Dim AvgCriteria As String
Dim AvgValue
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' For the given investment (ID), we need to calculate the average purchase date based on all "buy's"
' prior to the current year being processed, see "ForYear".  The process is simply a case of selecting all
' the "buy" DOE's for the current investment and calculating the average.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
AvgCriteria = "[InvstID] = " & ID & " AND [TType] = 1"
AvgValue = DAvg("CLng([DOE])", "Ledgers", AvgCriteria)
PurchaseDate = CDate(AvgValue)

End Function

[ranman256]

Why is this a function, and not a query?
(Yes, cLng is right, but why you have An avg date)

[GraeagleBill]

I tried a query but ran into data-type problems with trying to use the average function there as well. Anyway, I needed to get the section of the app up so I defaulted to what I know how to easily do. (Not sure what's behind your question regarding the averaging of dates.)

New code:

Private Function PurchaseDate(ID As Long)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' For the given investment (ID), we need to calculate the average purchase date based on all "buy's"
' prior to the current year being processed, see "ForYear".  The process is simply a case of selecting all
' the "buy" DOE's for the current investment and calculating the average.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim rs As DAO.Recordset
Dim strSql As String
Dim Accumulator As Long
Dim count As Integer

count = 0
Accumulator = 0

strSql = "SELECT DOE FROM Ledgers WHERE (InvstID = " & ID & " AND TTypeID = 1);"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)

Do While Not rs.EOF
    Accumulator = Accumulator + CLng(rs!DOE)
    count = count + 1
    rs.MoveNext
Loop

PurchaseDate = CDate(Accumulator / count)

rs.Close
Set rs = Nothing
End Function