Test if lapsed time is within limits

[Steve S]

I need to test to verify an athletes performance time is between upper/lower
bounds. most events have different u/l limits and I have these coded into a
table to check against. For instance event A, experience level 5 has a
minimum time of 1:30 (1 minute 30 seconds) and a maximum time limit of 2:45.
These values are stored in fields Ulimit and Llimit. The field on the form
that contains the performance time is PerTime.

any help is appreciated

 

[Mr B]

Steve,

You mention the event and the experience level but you do not indicate where
these values are comming from. You can use an Sql statement in code to
return the Upper and Lower values for on event and one experience level.
Hoever, before you can retrun the correct upper and lower values you will
have to supply the event and experience level to the sql statement. Once you
have provided the proper information to the sql statement, you would simply
open the recordset and read the two values The use an If statement to
evaluate if the Performance time is greater than the Lower limit and is less
than the upper limit.

I know that this is rather generalistic. If you would care to provide a
little more info, I know that some of us can help you get what you want.

 

[Steve S]

Maybe I need to rephrase my question. When i enter 0:30 into my table I get
12:30:00 AM. I don't care about clock time what I want to store and test
against is just 1 minute and 30 seconds. I am concerned about elapsed time
not clock time.

 

[Mr B]

Steve,

It sounds like you have the field in your table defined as a date/time data
type. Because this is just a value like any other value and not a date or an
actual time of day, use a Text type field for your value. then you can do the
compare other values against this value.

 

[Jamie Collins]

I need to test to verify an athletes performance time is between upper/lower
bounds. most events have different u/l limits and I have these coded into a
table to check against. For instance event A, experience level 5 has a
minimum time of 1:30 (1 minute 30 seconds) and a maximum time limit of 2:45.
These values are stored in fields Ulimit and Llimit. The field on the form
that contains the performance time is PerTime.

any help is appreciated

Do you mean a query? Something like

SELECT R1.athlete_number,
IIF(R1.PerTime BETWEEN T1.Llimit AND T1.Ulimit, 'T', 'F') AS
is_within_limits
FROM EventResults AS R1
INNER JOIN EventTimeLimits AS T1
ON R1.event_code = T1.event_code
AND R1.athlete_experience_level = T1.athlete_experience_level;

Jamie.

--

 

[Steve S]

Do you mean a query? Something like

SELECT R1.athlete_number,
IIF(R1.PerTime BETWEEN T1.Llimit AND T1.Ulimit, 'T', 'F') AS
is_within_limits
FROM EventResults AS R1
INNER JOIN EventTimeLimits AS T1
ON R1.event_code = T1.event_code
AND R1.athlete_experience_level = T1.athlete_experience_level;

Jamie.

Steve

 

[Jamie Collins]

I have to get past the initial problem. I have the Ulimit andLlimit fields as Date/Time fields in the table. When I enter 1:45 > for 1 minute 45 seconds it gets stored as 1:45:00 (1 hour 45 minutes 0 seconds)

Actually, logically it is #1899-12-30 01:45:00# but physically gets
stored as 7.29166666666667E-02.

Jet's DATETIME is suitable only for timestamp data (instants). Use a
column of type INTEGER (Long) to store units smallest time measure
e.g. seconds (or perhaps hundredths of one second) . Format the value
as desired for the front end.

Access's inbuilt formatting for DATETIME's is not enough justification
IMO for use on the wrong kind of data ;-)

Jamie.

--