473,387 Members | 1,404 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 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 2323
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Del | last post by:
Thanks in advance for any help. I have a database that was created in Access 2000. Several users have been upgraded to Access 2003. Since upgrading to 2003 we have noticed that some of the...
1
by: cdelaney | last post by:
I have a form that I created a calculation on using 2003. The calculation works exactly like I want it to but ONLY on the first and last record. The calculation does not work/exist on records in...
1
by: bmoos1 | last post by:
I have a form that when the monthly payment is entered, it calculates the annual payment. The correct numbers appear in the form, but they are not in the table or query from which the form was...
0
by: gavo | last post by:
Hi. using A2K; i have a form containing a continous subform. The question is, how can i call a calculation for one of the fields in the continous subform from the main form. At the moment i...
4
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The...
4
meLady
by: meLady | last post by:
Hello, I have a form called feedback with a subform called feedback Options. I did some counting calculation in master form "feedback" at the bottom page. the calculation is about count the...
2
by: campos | last post by:
Hi all, I ran into a headache problem. I have a windows form with a progress bar on it. Then I new a thread to do calculation for a long time. I want the progress bar to show the calculation...
14
klarae99
by: klarae99 | last post by:
Hello, I am working on an Access 2003 Database for inventory control. I am setting up a form (frmProducts) for the viewing and adding of product information. I have several tables that this...
5
kcdoell
by: kcdoell | last post by:
Hello: I thought I was done with this one but a user who is testing my DB just pointed out a problem. I used the following in the afterupdate event: Private Sub...
11
by: rajeevs | last post by:
Hi All I have two issues to put forward. First is bookmarking / or highlighting a particular record in a form. The form is continuous and the records are from a query result. One of the record...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

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.