Form Calculation

[DebbieG]

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

[Tom van Stiphout]

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

[DebbieG]

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(C urSSN As String, rptDate As Date)
' calling the function would look something like
' Me!TotalPoints = CurrentPoints(C urSSN,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 tblDriversInfra ctions WHERE "
strSQL = strSQL & "SSN = '" & CurSSN & "' AND InfractionDate <= #" &
rptDate & "#"
strSQL = strSQL & " ORDER BY InfractionDate"
Set rs = db.OpenRecordse t(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!PointsAssess ed
' store infraction date in array
pArray(1, RC) = rs!InfractionDa te
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("y yyy", pArray(1, Cnt), rptDate) +
Int(Format(rptD ate, "mmdd") < Format(pArray(1 , Cnt), "mmdd")))
Else
Yrs = Abs(DateDiff("y yyy", pArray(1, Cnt), pArray(1, Cnt +
1)) + Int(Format(pArr ay(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.c om...
| 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