Expressions

J

JohnM

I have boxes "Time Start", "Time Finish", "Duration" - What exprsession do I
put into "Duration" that shall calculate the time taken. I am new to access
 
A

Allen Browne

Firstly, your table should have only 2 fields, not all 3. The 3rd one is
dependent on the other 2, so it will not be stored: it will be a calculated
field instead.

If you want to store the start time and finish time, then you can calculate
the duration like this:
=DateDiff("n", [Time Start], [Time Finish])

If you want to store the start time and Duration (possibly a better choice),
you can calculate the finish time like this:
=DateAdd("n", [Time Start])

You can put that into the Control Source of a text box on your form/report.
Or, if you prefer, you can type the expression into the Field row in a
query, and use the query anywhere that you want to have all 3 fields.

The crucial aspect is that you must not store dependant data in a table.
Doing so breaks one of the basic rules of data normalization, and opens up
all sorts of maintenance issues that you would have to address, for no
benefit at all.
 
D

Douglas J. Steele

Slight typo:

=DateAdd("n", [Time Start])

should be

=DateAdd("n", [Duration], [Time Start])

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Allen Browne said:
Firstly, your table should have only 2 fields, not all 3. The 3rd one is
dependent on the other 2, so it will not be stored: it will be a
calculated field instead.

If you want to store the start time and finish time, then you can
calculate the duration like this:
=DateDiff("n", [Time Start], [Time Finish])

If you want to store the start time and Duration (possibly a better
choice), you can calculate the finish time like this:
=DateAdd("n", [Time Start])

You can put that into the Control Source of a text box on your
form/report. Or, if you prefer, you can type the expression into the Field
row in a query, and use the query anywhere that you want to have all 3
fields.

The crucial aspect is that you must not store dependant data in a table.
Doing so breaks one of the basic rules of data normalization, and opens up
all sorts of maintenance issues that you would have to address, for no
benefit at all.
Click to expand...
 
Top