difference between times returns a value

C

Charno

I have a table/form which i have 2 columns/fields which time is entered.
"DRSR Time" and "Gate Time".
What i'm trying to do is return the value 'Hit' (if the difference between
the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins),
in a 3rd column/field called "adhearance".

Any help would be great,

Thanks
 
S

Stefan Hoffmann

hi Charno,

I have a table/form which i have 2 columns/fields which time is entered.
"DRSR Time" and "Gate Time".
What i'm trying to do is return the value 'Hit' (if the difference between
the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins),
in a 3rd column/field called "adhearance".
Use DateDiff() in a query to calculate this value when needed:

IIf(DateDiff("n", [DRSR Time], [Gate Time]) > 60, "Miss", "Hit")

E.g.:

http://www.techonthenet.com/access/functions/date/datediff.php


mfG
--> stefan <--
 
R

RonaldoOneNil

Set the control source property of your adhearance field to
=IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")
 
C

Charno

I know its a bit of an Access sin, but could the value be returned to the
table?

RonaldoOneNil said:
Set the control source property of your adhearance field to
=IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")

Charno said:
I have a table/form which i have 2 columns/fields which time is entered.
"DRSR Time" and "Gate Time".
What i'm trying to do is return the value 'Hit' (if the difference between
the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins),
in a 3rd column/field called "adhearance".

Any help would be great,

Thanks
 
S

Stefan Hoffmann

hi Charno,

I know its a bit of an Access sin, but could the value be returned to the
table?
Yes, but this makes would allow you to get inconsistent data. Consider
that you have entered time date, inserted this miss/hit flag and the
changed the time data without recalculating it.

Thus you normally use this kind of calculation either in query or in a
field directly.

So you don't need to store it.


mfG
--> stefan <--
 
W

Wayne-I-M

Hi

When you speak of columns/fields it sounds to me as is you are working on
the table itself. The table is just there hold data and a few other (very
few) things.
You really should not be working on it.

If you to you can create a quesry based on the table and it will look a
little like the table.
You can then add a caculated column using the formual that Stefan and
Ronaldo gave you.
Don't store the results of a calculation - for lots of reasons. You can get
the results whenever you want. You can us the result with other
calculations, you can print it in a report, etc, etc. But the main thing is
that if the base of the calculation ever changes then the result will still
be correct.
OK there are a (very) few time when you should save the results - I do this
for a currency conversion calculation as I don't save the base (the amounts
and rates) as I am only interested in the result. But for almost all
calculation you really should not store the result.

Hope this helps


--
Wayne
Manchester, England.



Charno said:
I know its a bit of an Access sin, but could the value be returned to the
table?

RonaldoOneNil said:
Set the control source property of your adhearance field to
=IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")

Charno said:
I have a table/form which i have 2 columns/fields which time is entered.
"DRSR Time" and "Gate Time".
What i'm trying to do is return the value 'Hit' (if the difference between
the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins),
in a 3rd column/field called "adhearance".

Any help would be great,

Thanks
 
C

Charno

Thanks Wayne,
I kind of thought that i shouldn't be working on the table, but i thought i
would ask the question just incase i was missing something.
Using the formula's i was given i created a query and then added it as a
subform on my form.

Thanks to everyone for the help, much appreciated :)

Charno

Wayne-I-M said:
Hi

When you speak of columns/fields it sounds to me as is you are working on
the table itself. The table is just there hold data and a few other (very
few) things.
You really should not be working on it.

If you to you can create a quesry based on the table and it will look a
little like the table.
You can then add a caculated column using the formual that Stefan and
Ronaldo gave you.
Don't store the results of a calculation - for lots of reasons. You can get
the results whenever you want. You can us the result with other
calculations, you can print it in a report, etc, etc. But the main thing is
that if the base of the calculation ever changes then the result will still
be correct.
OK there are a (very) few time when you should save the results - I do this
for a currency conversion calculation as I don't save the base (the amounts
and rates) as I am only interested in the result. But for almost all
calculation you really should not store the result.

Hope this helps


--
Wayne
Manchester, England.



Charno said:
I know its a bit of an Access sin, but could the value be returned to the
table?

RonaldoOneNil said:
Set the control source property of your adhearance field to
=IIf(DateDiff("n",[DRSR Time],[Gate Time])<=60,"Hit","Miss")

:

I have a table/form which i have 2 columns/fields which time is entered.
"DRSR Time" and "Gate Time".
What i'm trying to do is return the value 'Hit' (if the difference between
the 2 times is less than 60 mins) or 'Miss' (if it is greater than 60 mins),
in a 3rd column/field called "adhearance".

Any help would be great,

Thanks
 
J

John W. Vinson

I know its a bit of an Access sin, but could the value be returned to the
table?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top