471,337 Members | 1,093 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

Form Calculation

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

Nov 13 '05 #1
2 2166
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


Nov 13 '05 #2
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

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by cdelaney | last post: by
reply views Thread by gavo | last post: by
4 posts views Thread by vg-mail | last post: by
2 posts views Thread by campos | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.