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

 

[Norie]

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]

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]

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.