Upfront apology - I am new to usenet groups.

I have done extensive due diligence to try to find my answer here, on
google, and on http://www.mvps.org/access

Regarding netiquette, being new to usenet, I first posted this at
http://www.eggheadcafe.com/forumpost...ostid=10177376

I am not purposefully double posting. I figured this was probably the
right place to post. Please forgive any indiscretion. I still need
help.

This is very lengthy, so to skip the verbose introduction, scroll to
"Needed Solution:"


Hello to all and thanks to the many Access MVP's that have contributed
so much to help us flunkies survive.

I have spent more than 16 hours trying to understand the problem that
I have encountered so I could begin to converse intelligently about it
and then find a solution.

I have concluded that it is a rather simple problem with two major
facets.

The first major facet of the problem is that I have not done any major
coding projects in the last decade. That means that things have
changed considerably since I last did any programming. It also means
that I don't remember anything that I did back then, something about a
mental block of a painful memory.

Seriously though, the problem that I am having is dealing with
calculated elapsed time vs. time duration or "lap times" as a keyed
entry.

At this new company I am working for we have finally updated to
Microsoft Office 2003. I'm glad we are only 7 years behind.

---------------

Situation:

I have been asked to create an Access Database to track man hours and
equipment hours.

For all of my calculations I have been using elapsed time as [TimeOut]-
[TimeIn]. It has worked great.


The problem is, now the boss wants to compare "combined lap times"
from the operator’s equipment logs to elapsed time from their
timesheets.

"lap times" relates to the amount of time it takes to complete work on
one row of material.

I have evidently forgotten my elementary math skills because I can't
seem to properly add minutes and seconds and then subtract them from a
larger block of time. I have gathered that the problem stems from
trying to compare apples and oranges or in this case nn:ss in a text
string to nn:ss in Date/Time format.

The "lap times" are entered simply as minutes and seconds in a text
field.

Table: EQ Row Details
Field: TimeInRow
Data Type: Text
Format: -blank-
Input Mask: :00:00;;_

With this structure I can simply type 4 digits on my form (1234) and
it is entered as :12:34. To me this means 12 minutes and 34 seconds.
I'm guessing that is not what it means in MS Access.

I chose to use this input mask to structure the text rather than using
the Date/Time format. I did this because I had the impression that
when I had a fields Data Type set to Date/Time and I entered 00:12:34
that it did not mean 12 minutes and 34 seconds, but that it
represented an actually point in time (i.e. 1/1/1900 00:12:34)

While this makes for extremely easy entry and viewing in my forms and
tables, I fear that my syntax is all messed up because my calculations
do not seem to reflect anything related to reality.

---------------

Many thanks for the information that has generously been given by
these knowledgeable individuals:

Allen Browne
Arvin Meyer
Crystal Long
Dave Hargis
Doug Steele
Jeff Boyce
John Spencer
John W. Vinson
Marshall Barton
Pat Hartman
Roger Carlson
And many others

In what follows you may find some odd humor in the misery of my
situation and my rambling about it. You will also see that I
seriously need to get a life. And, I need to take an in-depth Access
programming class. I wish these were options for this current
Database.
In the last two days I have viewed countless forums and help topics
with excellent suggestions and examples. I have downloaded and
installed several sample databases that work with different time
formats (Floating Point Date/Time and Text strings and integers) with
various methods of formatting, parsing, and calculating. After all of
this, my brain is tied up in knots and I can't seem to make any of it
work with my particular scenario. I feel like a kindergartener
attending a graduate level course on Visual Basic and am completely
lost.

I decided I needed to make a choice. I decided that it would be
better to take my frustrations out on the keyboard by writing this to
beg for your help rather than taking my frustrations out with my
keyboard and destroying my antique workstation in a violent outburst
of retaliation against an inanimate object. So, please forgive my
diarrhea of the mouth. And if you know any good therapists that can
untie my brain from the knots I have created, please, do tell.

-----------------------
The Plea:
I am at an impasse. I need your help. After two fruitless days of
trying to figure out basic math I feel incompetent. I have tried so
many approaches that I have come to realize that I just can't grasp
the language needed to program the multiple conversions that need to
take place to accomplish this job. I know what I need and I know what
has to happen to accomplish it. I just can't translate that into the
language necessary to get Access to accomplish the needed format
conversions and mathematical operations. So, while it is basic stuff,
I am unable to wrap my head around the scope and complexity of the
code.

This will take some time and I truly value your time. After two lost
days I have taken 7 more hours to try to process my dilemma and fully
explain it in a way that you might be able to help. So if you are up
for it, I am willing to compensate your efforts. Please see the
details at the end of the post.

------------------------------
------------------------------

Needed Solution:

A basic yet functional *.mdb database file with supporting code.
(For use in MS Access 2003)

To fully explain the scenario and the solution needed, I have
included:

An explanatory scenario with Data inputs and needed results.
A basic database design.
An Envisioned Form layout to display results.


------------------------------
------------------------------

Explanatory Scenario

------------------------------
I this scenario

An employee does a particular job.
It takes 2 hours and 15 minutes.
It is recorded on a time card.
Time In of 8:30 AM
Time Out of 10:45 AM
Time Format hh:nn on 12 hour clock.

A piece of equipment is used for the job.
The equipment runs for 1 hour and 30 minutes.
The hour meter times are recorded on an equipment log.
Beginning Meter Hours of 40382.25
Ending Meter Hours of 40383.75
Time format is total cumulative hours of use to an accuracy of
hundredths of an hour.
For my scenario I will represent that time in the syntax of hhhhh.hh
(I don't know the proper syntax).

The equipment is used to do work on 5 individual rows of material.
The work is billed out according to the actual time spent in the row.
The time spent in the row is clocked by the employee with a stopwatch.
The time in each row is recorded on a billing worksheet and shown
respectively as:
17:20 (17 minutes and 20 seconds)
11:32 (11 minutes and 32 seconds)
08:27 (8 minutes and 27 seconds)
14:47 (14 minutes and 47 seconds)
09:18 (9 minutes and 18 seconds)
15:59 (15 minutes and 59 seconds)
For a combined total of 77 minutes and 23 seconds.
Time format is nnn:ss to show total minutes and seconds without using
hours in the expression.

There are thousands of records to enter. Entry needs to be in the
format that it was originally handwritten in to expedite the process
and to eliminate hand conversion errors prior to entry.

To solve the problem I need to convert the three various time formats
to a normalized format. Then calculations can be done and comparisons
can be made using an “apples to apples” approach. (Not comparing
apples to oranges.)

Then I need to calculate how many man hours are needed to operate the
equipment for a given number of hours.

And I need to calculate how many equipment hours are needed to produce
a given amount of billable time.

And I need to calculate how many man hours are needed to produce a
given amount of billable time.

With these numbers, I can then calculate other inputs to create job
costs and profitability figures.


------------------------------
------------------------------

Basic Database Design

------------------------------

Table: Timesheet
Field: Auto Numbered Primary Key
Field: "TimeIn" with Data Type: Date/Time
Field: "TimeOut" with Data Type: Date/Time

Table: EquipmentHours
Field: Auto Numbered Primary Key
Field: "BeginHours" displaying hhhhh.hh (??Syntax??) in any format
(meter hours to two decimal accuracy)
Field: "EndHours" displaying hhhhh.hh (??Syntax??) in any format
(meter hours to two decimal accuracy)

Table: RowTime
Field: Auto Numbered Primary Key
Field: "RowTime" displaying :nn:ss in any format
-----

Form: Timesheet
Field: "TimeIn"
Field: "TimeOut"
Calculated Control: ElapsedJobTime (TimeOut-TimeIn)

With 2 subforms.
(Two different subforms are needed for different functions - These are
just the critical elements that are related to this problem).

Subform1: EquipmentHours
Field: "BeginHours"
Field: "EndHours"
Calculated Control: TotalMeterHours (decimal format of EndHours-
BeginHours)

Subform2: RowTime (as a continuous subform)
Field: "RowTime"
Calculated Control: TotalRowTimes (a sum of multiple row times
displayed as nnn:ss)

Main Form Control: Display of first subforms Calculated Control:
TotalMeterHours

Main Form Control: Display of second subforms Calculated Control:
TotalRowTimes

3 Main Form Controls to display each total in Normalized format.

Both the Label and the format are changed to reflect the conversion
necessary for calculations in these three controls.

Source Normalized display

ElapsedJobTime JobTime
2:15:00 02:15:00
TotalMeterHours EquipmentTime
1.50 01:30:00
TotalRowTimes TimeInRow
77:23 01:17:23



Main Form Calculated Control: Displaying the difference between
JobTime and EquipmentTime

Main Form Calculated Control: Displaying the difference between
EquipmentTime and TimeInRow

Main Form Calculated Control: Displaying the difference between
JobTime and TimeInRow


------------------------------
------------------------------

Envisioned Form Layout

------------------------------

Generic Form Title

TimeIn TimeOut ElapsedJobTime
8:30 AM 10:45 AM 2:15:00

BeginHours EndHours TotalMeterHours
40382.25 40383.75 1.50

restated in Normalized Values (Comparing Apples to Apples)
JobTime EquipmentTime TimeInRow
02:15:00 01:30:00 01:17:23

RowTime TotalRowTimes
:17:20 77:23
:11:32
:08:27
:14:47
:09:18
:15:59

Example of Summary Calculation

JobTime-EquipmentTime
00:45:00

EquipmentTime-TimeInRow
00:12:37

JobTime-TimeInRow
00:57:37

--------------------------------
--------------------------------

Terms:

I am an entry level, hourly employee at the beginning of a new
career. I am between a rock and a hard spot with a significant task
to complete and few resources aside from my personal time. I don't
have the money to personally pay a consultant what this is worth. Nor
is the boss willing to pay for it. That being said, I highly value
your time and I am willing to compensate your efforts by paying-it-
forward through in-kind contributions.

I will donate time to a positive local community project in the amount
of twice the time you put into this solution. I will let you know
what I do and what the results are.

And

I will donate two hours worth of my pay to a charity/cause of your
choosing in your honor.

And

If you would like and with your permission, once completed, I will
donate and make publicly available, my fully functional completed
database with acknowledgement of your contributions to it. That would
be the solution to the dilemma of time calculations utilizing three
different time formats.

My terms are very negotiable to make it worth your effort.

Thanks In Advance

RED Garner
redgarner at gmail dot com
c#208.308.2345
From southern Idaho, USA

The right way to approach this is to convert your times to seconds,
sum the seconds and format the seconds as hh:mm:ss

Some years ago, I created and posted to this group a pair of
functions for that puropse, google Dur2Sec() and Sec2Dur()

http://groups.google.com/group/comp.databases.ms-
access/browse_thread/thread/5a5fb89b724b895b/7f4904fd8286f10f?
lnk=gst&q=seconds+quintal#7f4904fd8286f10f


Public Function sec2dur(seconds As Long) As String
'converts a start and stop time to number of seconds. and
'then displays as hhh:mm:ss

On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

sec2dur = Format(hrs, "#,##0") & ":" & Format(mins, "00") & ":" &
Format(secs, "00")

End Function

Bob

RED <jbox.red (AT) gmail (DOT) com> wrote in
news:c39d6074-155e-46ea-ba25-0c11465ae121 (AT) 23g2000pre (DOT) googlegroups.com
:

On Jun 9, 4:28*am, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:

Bob, Thanks so much for your response. I feel that you are correct in
that full conversion to seconds is the right way to approach this.
That is the conclusion that I came to after two days of stumbling over
myself. After writing the code in and failing, and copying vb modules
in and failing, and tons of trial and error, I still can't make it
work. I know I must be just missing something very simple because
everything I have come across is very easy to handle. I guess that I
am just not referencing the code correctly. My trouble is that since
I have been away from programming for so long that I am having trouble
connecting the dots. If you know of a sample database that uses these
functions, please kindly point me in that direction. Then I can see
the code in action as it is used in different controls. I think that
will help me see where I am missing the boat. Again, Thanks - RED

RED wrote:
You could do this as a test. Create Table1, Field EQ, type Text. Save
and enter a few records. Then create the queries below to run as tests.

I created a table; Table1. It contains the field EQ to store the time.
Since you are guaranteed the field will contain 4 numbers to represent
the time, minutes on the left 2 char, the minutes the right 2. To do
math, you need to convert the values from text values to numbers. Ex:

SELECT Table1.EQ, CLng(Left([EQ],2)) AS Minutes, CLng(Right([EQ],2)) AS
Seconds
FROM Table1;

The above statement splits the minutes from seconds. I saved the above
query as Query1. To do so, create Table1 first and enter a few records.
Then copy the above statement to the clipboard. From the database
window select Query/New/Design View/Close and then from the Query Menu
View/SQL and pasted the above SQL statement.

Now, if you wanted the Sum of these values, you could run a query like
SELECT Sum(CLng(Left([EQ],2))) AS TotMinutes, Sum(CLng(Left([EQ],2)))*60
AS TotMinSecs, Sum(CLng(Right([EQ],2))) AS TotSeconds
FROM Table1;

I created and saved the above SQL statement using the steps above and
saved the query as Query2. If you noticed, I also added a third column
to convert Minutes into seconds; TotMinSecs.

In the records I added, I had 92 minutes ( or 5520 total seconds) and
101 seconds.

Using the function provided by Bob I can enter from the immediate window
the following
? Sec2Dur(Clng(Dlookup("TotMinSecs","Query2")) +
Clng(Dlookup("TotSeconds","Query2")))

Bob's functionreturned back to me 1:33:41

I then modified Bob's code. You now pass an additional paramenter. If
you passed "HMS", you get 1:33:41. If you passed "HS" or anything else,
the program, in my case, returnes 93:41. Here's Bob's modified code.
Go to Modules, new, and paste it into the code window. If you have an
existing function called sec2dur remove or rename it to avoid code
conflicts.


Public Function sec2dur(seconds As Long, strHM As String) As String
'converts a start and stop time to number of seconds. and
'then displays as hhh:mm:ss

On Error Resume Next

Dim hrs As Long
Dim mins As Integer
Dim secs As Integer

hrs = Int(seconds / 3600)
mins = Int((seconds - (3600 * hrs)) / 60)
secs = seconds - (hrs * 3600 + mins * 60)

If strHM = "HMS" Then
sec2dur = Format(hrs, "#,##0") & ":" & _
Format(mins, "00") & ":" & Format(secs, "#,##0")
Else
sec2dur = Format((hrs * 60) + mins, "#,##0") & ":" &
Format(secs, "#,##0")
End If
End Function

On Jun 9, 10:10*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Thanks Salad

I will get to work on this and let you know the results tomorrow.

THANKS - THANKS - THANKS