Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Question Unanswered: Module that caculates the difference between times

    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.

    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. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's what I do for this:

    1. Create a function in a module as such:

    Function retDateDiffDays(StartDate as date, EndDate as date) as variant
    retDateDiffDays = datediff("d", StartDate, EndDate) '<- change to meet your needs
    End Function

    or

    Function retDateDiffHours(StartDate as date, EndDate as date) as variant
    retDateDiffHours = datediff("h", StartDate, EndDate) '<- change to meet your needs
    End Function

    or

    Function retDateDiffMins(StartDate as date, EndDate as date) as variant
    retDateDiffMins = datediff("n", StartDate, EndDate) '<- change to meet your needs
    End Function

    or
    Function retDateDiffTot(StartDate as date, EndDate as date) as variant
    retDateDiffTot = EndDate - StartDate '<- change to meet your needs
    End Function

    2. Then to return the # of days between 2 dates (which you can use as an expression in a query, or as the Control Source for a field on a form or report, or as part of any other calculations, or to write to a field on the form):

    =retDateDiffDays([MyStartDateField],[MyEndDateField])
    or
    =retDateDiffHours([MyStartDateField],[MyEndDateField])
    or
    =retDateDiffMins([MyStartDateField],[MyEndDateField])

    or to update a field on my form (that's bound to a field called: MyDateDiffField in the table):
    me!MyTripTimeField =retDateDiffDays([MyStartDateField],[MyEndDateField])

    Note that in the function above, the 2 values being passed to the function MUST be valid date values. If for some reason you need to pass a value that is not in a date format (for other types of functions), I will use code as such:

    Function retDateDiffDays(StartDate as variant, EndDate as variant) as variant
    retDateDiffDays = datediff("d", StartDate, EndDate) '<- change to meet your needs
    End Function

    or to pass only 1 date value:

    Function retDateDiffDays(MyDate as date) as variant
    retDateDiffDays = datediff("d", MyDate, Date()) '<- change to meet your needs
    End Function

    The above will return the difference in days between whatever is passed to the function as MyDate and today's date.

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

    I might concatenate the Date with the time field (but FIRST rename the Date field if that is actually what you named it in the table!!!) Don't ever name a field just Date!

    ex:
    dim DateToPassToFunction1 as date
    DateToPassToFunction1 = [MyDate] & " " & [StartTime] '< - note - no spaces in Start Time. Rename this also if you have a space in the name in the table. Spaces are killers for coding.
    Dim DateToPassToFunction2 as date
    DateToPassToFunction2 = [MyDate] & " " & [EndTime] '<- again, get rid of spaces in the name in the table!

    me!TripTime = retDateDiffDays(DateToPassToFunction1, DateToPassToFunction2)
    Last edited by pkstormy; 08-07-10 at 19:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •