Module that caculates the difference between times

[CSUjr]

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

 

[Molly Coddlez]

Lets see how much of this i can address for you.

1 a) not sure i understand this, what do you mean by "uses" a date? is the info being input manually through a form or are you importing the data from the spreadsheet by code?
1 b) i have a couple of functions here that might help

Public Function fRemoveSeconds(dteTimeWithSeconds as Date) as Date
Dim dteJustSeconds as Date

   dteJustSeconds = format(cdate("00:00:" & cstr(second(dteTimeWithSeconds))),"hh:mm:ss") 'outputs just the seconds 00:00:ss

   fRemoveSeconds = CDate(dteTimeWithSeconds - dteJustSeconds)

End Function

or, if you needed to round up to the nearest minute

Public Function fRemoveSeconds(dteTimeWithSeconds as Date) as Date
Dim dteJustSeconds as Date, dteReturn as Date

   dteJustSeconds = format(cdate("00:00:" & cstr(second(dteTimeWithSeconds))),"hh:mm:ss") 'outputs just the seconds 00:00:ss

   dteReturn = CDate(dteTimeWithSeconds - dteJustSeconds)

   if CInt(cstr(second(dteTimeWithSeconds)))) >=30 Then dteReturn = dteReturn + CDate("00:01:00")

   fRemoveSeconds = dteReturn

End Function

2) as long as you have a table set up for them to output to. I would suggest not using an AutoForm or binding datafields to your input boxes if you are inputting manually, and setting the inputs up in your input form in code. If you need a hand with this just gizza shout

3) presuming that you followed my advice in 2) i'd fire the trip time calculation either when both start and end times are correctly input, or at the point where you click the command button to say you're done inputting that record. it all depends on whether you care if the inputter sees the time taken straight away or not.

if you want to modify empty triptime feilds in your table, you could use the functions in an update query

Hope this is helpful