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?)
Module that caculates the difference between times
me![Trip Time] = [End Time]-[Start Time]
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])