Module that caculates the difference between times

Discussion in 'MS Access' started by CSUjr, Aug 7, 2010.

  1. CSUjr

    CSUjr New Member

    Joined:
    Aug 7, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Retired
    Location:
    NW Washington State, USA
    First, I'm running Windows 7 and working in Access 2007.

    I'm new at VBA programming and creating Modules so I'd like to ask for some help with creating a Module that caculates the difference between times and then populates a field in the Table, the Form and the Report.

    The reason that I'm asking for help creating a Module for this is that I've tried using the datediff() function, and also combining it with the format() function and in both cases have been unsuccessful. :worried:

    So, here are the circumstances;

    I am creating an Access database for some Tractor Trailer Logs.

    This Access database is based on data imported from an Excel spreadsheet.

    One of the fields in the Excel spreadsheet is labeled [Trip Time](T).

    This field is populated through a simple arithmatic formula that subtracts values from two previous fields, which are, [Start Time](R) & [End Time](S).

    The format of those fields, [Start Time](R) & [End Time](S), is h.mm AM/PM

    The formula (in Excel) that populates [Trip Time](T) reads (=Snn - Rnn) and the output format of that field is (Short Time) h.mm, for instance 2:30 (2 1/2 hours).

    So being somewhat new to Access 2007 and having no experience creating Modules, I'd like to ask for some help to;

    1. Create a working Module that;
    a. uses a single date - or uses the same date twice. (because the Logs currently show all trips occuring on the same date)
    b. that does not use Seconds (.ss) in the caculations

    2. If necessary, how to tailor the Code to work correctly with the existing labeled fields ([Date], [Start Time], [End Time], [Trip Time].

    3. How and where to implement the Module so that the [Trip Time] field is populated in the Table, the Form and the Report.

    (If only within the Form during data entry, then how can I make it so that the [Trip Time] field is also popualted within past, existing Log entries?)

    With appreciation,
    Charles
     
  2. apr pillai

    apr pillai New Member

    Joined:
    Dec 17, 2010
    Messages:
    16
    Likes Received:
    12
    Trophy Points:
    0
    Occupation:
    Auditor
    Location:
    India
    Home Page:
    http://www.msaccesstips.com/
    1. Open the Form in Design View
    2. Click on the [End Time] Field
    3. Click on the Property Sheet icon on the Toolbar to display the Property Sheet of the Field.
    4. Find the Lost Focus Property and click on it
    5. Select [Event Procedure] from the drop down control and click on the build (...) button to open the VBA Module.
    6. A skeletion of a VBA routine will be inserted in the module.
    7. Write the following line of VBA Code:
    Code:
    me![Trip Time] = [End Time]-[Start Time]
    
    After you enter the [End Time] and the insertion point is moved out of the field the [Trip time] will be calculated and inserted into the [Trip Time] Field. To display the time in hh:nn format set this format string in the Format Property of the [Trip Time] field.

    Internaly the time is stored as a real number like 0.5 for 12:00 noon, the format string formats in the way you want it to be displayed.

    If the Date is likely to change between Trip Start and End Times then you must modify the above statement as below:

    Me![Trip Time] = ([End Date]+[End Time]) - ([Start Date]+[Start Time])
     
    CSUjr and shabbir like this.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice