Problem with comparison VB code

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hey, I came across a problem and I was wondering if you
could help me. I have a form with 8 competitors' names &
times after a swimming race. I want to compare each time
and display the quickest time.

So far I have got 8 fields of times all formatted in long-
time format. I have another field called overall best
time. I need to compare the times and display the
quickest in overall best time.

This is the code so far - but dont think I'm correct:

Me![OverallBest] = Me![Entrant1Time]
If Me![Entrant2Time] < Me![OverallBest] Then Me!
[OverallBest] = Me![Entrant1Time]

What this idea was meant to do, was to put the first time
in the overall best time, then compare it with the
second. If the second was smaller than the overall best,
then it would be replaced
YEH - I KNOW IVE MADE A MESS OF IT

Any help would be greatly appreciated.

If you need to see it, U can always reach me via MSN
messenger and Remote assistance.
MSN Address is (e-mail address removed)

Thankx Again!

Paul
 
This is a situation that would have been better to put in a multiple record
type deal, then you could loop through each record and compare which is the
best time. This particular situation makes me think of how phone numbers
use to be stored when there were only like 3 numbers (Work, Home and
Emergency), but now, there are like 7+ different types of phone numbers, so
the solution to this phone number deal was to create 2 new tables like the
following, and get rid of the 3 phone number fields after the conversion has
taken place:

Table 1, Type of Phone Numbers

Field 1: ID
Field 2: Description (I.e. Work, Cell, Fax)

Table 2, Phone Numbers

Field 1: Phone Number ID
Field 2: Individual ID
Field 3: Type of Phone ID
Field 4: Phone Number

As the old saying goes, it's cheaper to add records than it is to add
fields.

Now use this same idea for your swim racing events. One table contains the
various different swim events (100 Meter, 200 Meter, 400 Meter, 100Meter x 4
Relay, etc) similar to the Type of Phone Numbers table above, and the other
table contains the actual individuals with the Individual/Team-Time ID,
Individual/Team ID, Event ID, Date/Time, Individual/Team Race Time (how much
time did it take the individual/team to complete the race/course) and any
other pertinent related information for that particular table, which is
similar to the Phone Numbers Table above.

Once you get the 2 tables setup, you could use ADO/DAO coding along with Do
While/Until...Loop and For I = 1 To n Step 1...Next coding to convert the
data from the old format to the new format.

Once the data is converted over, you could then use ADO/DAO coding to pull
the required data and limited to which events more easily, and use the Do
While Not rst.EOF...Loop coding to have your comparisons done within the
recursive looping, or even better yet, use the aggregated Min function on
the Race Time column, if not concerned with who got it, or if is concerned
with who got it, sort the recordset by race time in ascending order and then
go to the first record of the recordset. The only thing you would need to
check for in this latter case, check the following records after the first
record for any ties. If you need to limit how many records are returned,
you can also use the TOP predicate within the SELECT clause like:

SELECT TOP 10 *

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Paul said:
Hey, I came across a problem and I was wondering if you
could help me. I have a form with 8 competitors' names &
times after a swimming race. I want to compare each time
and display the quickest time.

So far I have got 8 fields of times all formatted in long-
time format. I have another field called overall best
time. I need to compare the times and display the
quickest in overall best time.

This is the code so far - but dont think I'm correct:

Me![OverallBest] = Me![Entrant1Time]
If Me![Entrant2Time] < Me![OverallBest] Then Me!
[OverallBest] = Me![Entrant1Time]

What this idea was meant to do, was to put the first time
in the overall best time, then compare it with the
second. If the second was smaller than the overall best,
then it would be replaced
YEH - I KNOW IVE MADE A MESS OF IT

Any help would be greatly appreciated.

If you need to see it, U can always reach me via MSN
messenger and Remote assistance.
MSN Address is (e-mail address removed)

Thankx Again!

Paul
Click to expand...
 
Back
Top