Module that caculates the difference between times

CSUjr

New Member
Joined
Aug 7, 2010
Messages
2
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,764
Office Version
365
Platform
Windows
Charles

Why do you think you need a module/code?

Access can do calculations, perhaps not everything Excel can but it should be able to handle what you describe.

You say you tried DateDiff but it didn't work, how did it not work?

Even if you did create you own function I don't think you'd want to make it specific to your current fields.

A function that only works with a particular table/field(s) seems kind of redundant/restricted.:)

Another thing is that you probably shouldn't have calculated fields in a table.

Any calculations should be done in queries.
 

CSUjr

New Member
Joined
Aug 7, 2010
Messages
2
Thank you for your reply.

Like I said, I tried to write a query using the datediff() function, and also combining it with the format() function and in both cases, the [Trip Time] field remained blank.

Why I think that a Module would work is that once I create a Function/Module, I might be able to put an Unbound Text Box, in the Form's ControlSource.

In any case, I need help in calculating the difference between times that are declared in 3 related fields, Date, Start Time & End Time - and then have the result of that calculation show up in a field labeled Trip Time.

Can you, or someone here help me accomplish that?

With appreciation,
Charles
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,764
Office Version
365
Platform
Windows
Charles

You should be able to use basically the same calculation as you would in Excel.

If you are having problems it could be something to do with the data - the times might not be getting recognised as times.

If you were using Format that could be the case, that function returns text.

Just like in Excel dates/times are stored in Access as numbers.

For time 1 represents 1 day, ie 24 hours.

So 0.5 is 12 hrs etc.

Can you post some sample data?

I've tried creating some myself but I'm all hours and minutes with that sort of thing.

Always seem to end up with start times in the 19th century and finish times in the 25th.:)

Any formatting should be done on the result of the calculation, just like you would in Excel.

You could use Format but there are also various formatting options available for fields/controls etc.

As for unbound controls, you can populate them with the result of a calculation without needing a module/code.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,712
Messages
5,512,991
Members
408,928
Latest member
Black Vinyl

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top