• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Access 2003 field to calculate time durations

I am using Access 2003, and I am a NEWBIE @ Access, I have 2 fields on a form, START TIME and END TIME. I want to add a field for the duration of time entered. So if the user entered START TIME 10:00 AM and END TIME 11:00 AM the field will populate 1 hour.

The time duratoin will never exceed 24 hours, in fact the duration of time will never exceed 2-3 hours usually. This is for horse riding lessons, and the horses never usually get assigned to a student for more than 1-2 hours at a time.

thx experts...
BobR
0
bobrossi56
Asked:
bobrossi56
  • 2
  • 2
3 Solutions
 
GRayLCommented:
You should never store a derived result - in this case duration - as it is always available via a query.  

SELECT Rnd(DateDiff("n",[Start Time], [End Time])/60,1) As Duration FROM Mytable

Of course you can select any number of fields before and after the Duration calculation.
0
 
Patrick MatthewsCommented:
I agree completely with GRayL: don't store this; instead calculate on demand with a query.

I would calculate it a bit differently, though:

SELECT StartTime, EndTime, 24 * (EndTime - StartTime) AS Hrs
FROM SomeTable

Open in new window

0
 
bobrossi56Author Commented:
Great tips guys, and I agree, doing this in a query is fine. I just created a new query using the tblHorseAssign, and I included all the fields in that table, but as a NEWBIE I am clueless where I add this SQL statement to the new query.
thx
0
 
bobrossi56Author Commented:
Actually I jut figured out how to add this to a query. BUT.....How do I add it to a form. I need also a field on the form calculating the duration.
thx
0
 
Patrick MatthewsCommented:
On a form, just add an unbound textbox, and in the control source for that textbox, use:

=24 * (EndTime - StartTime)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now