Access 2003 field to calculate time durations

Posted on 2011-10-02
Last Modified: 2013-11-28
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...
Question by:bobrossi56
    LVL 44

    Assisted Solution

    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.
    LVL 92

    Accepted Solution

    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


    Author Comment

    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.

    Author Comment

    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.
    LVL 92

    Assisted Solution

    by:Patrick Matthews
    On a form, just add an unbound textbox, and in the control source for that textbox, use:

    =24 * (EndTime - StartTime)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now