Tom,
No, I'm working with a big pig farm!
I got a solution elsewhere and thought I would share in case it might help
you also. It works like a charm in a form -- we'll see if I can get it work
on a report.
Debbie
Public Function CurrentPoints(CurSSN As String, rptDate As Date)
' calling the function would look something like
' Me!TotalPoints = CurrentPoints(CurSSN,Date)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim runTot As Double
Dim pArray()
Dim Yrs As Integer
Dim RC As Integer
Dim Cnt As Integer
Set db = CurrentDb
strSQL = "SELECT * from tblDriversInfractions WHERE "
strSQL = strSQL & "SSN = '" & CurSSN & "' AND InfractionDate <= #" &
rptDate & "#"
strSQL = strSQL & " ORDER BY InfractionDate"
Set rs = db.OpenRecordset(strSQL)
runTot = 0
' step through each record totalling points with each step
Do While Not rs.EOF
' redimension array as each record is accessed
ReDim Preserve pArray(1, RC)
' store points assessed in array
pArray(0, RC) = rs!PointsAssessed
' store infraction date in array
pArray(1, RC) = rs!InfractionDate
rs.MoveNext
RC = RC + 1
Loop
rs.Close
db.Close
' now go through array to calculate running total
For Cnt = 0 To RC
' add points to running total
runTot = runTot + pArray(0, Cnt)
If Cnt < RC Then
On Error Resume Next
' compare date of infraction to next record's date of infraction
' if there is no next record, compare with rptDate
If IsNull(pArray(1, Cnt + 1)) Then
Yrs = Abs(DateDiff("yyyy", pArray(1, Cnt), rptDate) +
Int(Format(rptDate, "mmdd") < Format(pArray(1, Cnt), "mmdd")))
Else
Yrs = Abs(DateDiff("yyyy", pArray(1, Cnt), pArray(1, Cnt +
1)) + Int(Format(pArray(1, Cnt + 1), "mmdd") < Format(pArray(1, Cnt),
"mmdd")))
End If
' perform reductions
If Yrs >= 1 Then
runTot = runTot * 2 / 3
End If
If Yrs >= 2 Then
runTot = runTot / 2
End If
If Yrs >= 3 Then
runTot = 0
End If
End If
Next
CurrentPoints = runTot
End Function
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:im********************************@4ax.com...
| On Sun, 27 Feb 2005 20:16:14 -0600, "DebbieG" <de*****@NOSPAM.NOSPAM>
| wrote:
|
| Your requirements are virtually identical to those of a client of
| ours. You're not working for a large snackfood manufacturer, right?
|
| Essentially, every time the app starts (or whenever you're about to
| review points or print reports) it needs to loop over all employees
| and determine if a new record should be added (e.g. it's been 1 year
| since the last infraction - add a record that reduces points by 1/3).
|
| The final score can indeed be calculated by a textbox in the footer of
| a form or report. The controlsource would be:
| =Sum(Points)
| In our case it was calculated differently, because infractions would
| expire after several years. The DSum function could be used.
|
| Regards,
|
| -Tom.
|
|
|
| >I have no idea how to start with this one.
| >
| >I have a subform where records could look similar to:
| >
| >Infraction Date Points
| >01/01/2000 3
| >06/01/2002 1
| >
| >Somehow, I've got to calculate the points the driver has as of the
current
| >date. For instance, in the above example:
| >
| >On 01/01/2000 three points added to the driver's record.
| >On 01/01/2001 the points were reduced by 1/3 (2).
| >On 01/01/2002 the remaining points were reduced by 1/2 (1).
| >On 06/01/2002 one point is added to the remainder (2).
| >On 06/01/2003 the remaining points were reduced by 1/3 (1.33).
| >On 06/01/2004 the remaining points were reduced by 1/2 (0.65).
| >
| >On 06/01/2005 the remaining points will be 0 if no more infractions.
| >
| >I'm thinking of putting an unbound textbox in the footer of the subform
that
| >will show the remaining points but have no idea how to do this
calculation.
| >
| >Can someone point me the right direction.
| >
| >I'll probably have to create a report with the same calculation.
| >
| >TIA,
| >Debbie
