CALCULATING TIME

[luscus]

I am trying to device a formula so that when i check of a yes/no box
(done) it will automatically add the time in a field called "End
Time" and at the same time stamp the amount of minutes between a field
"begining Time" ( which already automatically stamps the time as you
enter a new record) and the field "end time"

For example : If I begun the new record at 2:45 PM worked on the
problem untill 3:00 PM and solved it. I would then proceed to check of
the box in the field "Done" this would then stamp 3:00 PM in the field
"End Time", and also would stamp 15 in the "Total Time" Field.

I have spent about 15 hours reading in the Microsoft help and I am as
ignorant on how to do this as before I begun.

Thanks for the help

manuel

[Tom van Stiphout]

On 4 Feb 2005 06:04:02 -0800, "luscus" <LU****@NETZERO.NET> wrote:

In the checkbox' AfterUpdate event write one line:
txtEndTime = Time
(replace txtEndTime with the name of your text box)

Total Time is a different story. It is what's called a Calculated
Field, and should not be stored in the database. Rather you
recalculate it when you need it, typically in a query.
So you create a new query, select your table, drag some fields to the
grid, and in a new field write:
TotalTimeInMinutes: DateDiff("n",BeginTime, EndTime)
(replace BeginTime and EndTime with your field names)

-Tom.

I am trying to device a formula so that when i check of a yes/no box
(done) it will automatically add the time in a field called "End
Time" and at the same time stamp the amount of minutes between a field
"begining Time" ( which already automatically stamps the time as you
enter a new record) and the field "end time"

For example : If I begun the new record at 2:45 PM worked on the
problem untill 3:00 PM and solved it. I would then proceed to check of
the box in the field "Done" this would then stamp 3:00 PM in the field
"End Time", and also would stamp 15 in the "Total Time" Field.

I have spent about 15 hours reading in the Microsoft help and I am as
ignorant on how to do this as before I begun.

Thanks for the help

manuel

[Rick Brandt]

luscus wrote:

I am trying to device a formula so that when i check of a yes/no box
(done) it will automatically add the time in a field called "End
Time" and at the same time stamp the amount of minutes between a field
"begining Time" ( which already automatically stamps the time as you
enter a new record) and the field "end time"

For example : If I begun the new record at 2:45 PM worked on the
problem untill 3:00 PM and solved it. I would then proceed to check of
the box in the field "Done" this would then stamp 3:00 PM in the field
"End Time", and also would stamp 15 in the "Total Time" Field.

I have spent about 15 hours reading in the Microsoft help and I am as
ignorant on how to do this as before I begun.

You have some basic design flaws in your table structure (easily remedied
though).

First off any record where [End Time] is not Null will by definition be
"Done" so you do not need a separate Yes/No field to indicate done you
should eliminate that field and replace the CheckBox on your form with a
button that simply fills in the [End Time] field.

Me![End Time] = Now()

Second, since the difference in minutes between [Start Time] and [End Time]
can be easily calculated on-the-fly, there is no reason to have a separate
field to store that value either. That field should also be eliminated from
your table. On your form all you need is a TextBox with the expression...

=DateDiff("n", [Start Time], [End Time])

To eliminate having to write this expression multiple times in various forms
and reports you can create a query that includes all the data from your
table, but also adds a calculated field [Total Time] using the same
expression. Then you simply use the query every place you are now using
your table. You could also add a calculated field to the query to give you
your [Done] Yes/No field with = Not IsNull([End Time])

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Ed Robichaud]

Not sure how you'll handle cases where the user clicks the "Done" checkbox
to undo, but:
1. in the On Change event of the "Done" checkbox, put:
If Me!myDone = -1 Then
Me!myEndtime = Now()
Else
End if

-substitute the names of your done checkbox and end time controls
This puts the current system time in the End Time control (and stores it in
the table when form is closed) anytime that the user puts a check in "Done".

2. create an unbound text box on your form for "TotalTime"; make the
control source: =DateDiff("n",[Starttime],[Endtime])

This calculates the time difference between "Start" and "End" and displays
the result. That value is not stored, but can be recalculated whenever
needed fro other forms/reports.

That's it!

BTW, it's generally better to not have spaces in your field/control names.
If you do, always enclose them in square brackets "[ ]" when referring to
them by name.
-Ed
"luscus" <LU****@NETZERO.NET> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I am trying to device a formula so that when i check of a yes/no box
(done) it will automatically add the time in a field called "End
Time" and at the same time stamp the amount of minutes between a field
"begining Time" ( which already automatically stamps the time as you
enter a new record) and the field "end time"

For example : If I begun the new record at 2:45 PM worked on the
problem untill 3:00 PM and solved it. I would then proceed to check of
the box in the field "Done" this would then stamp 3:00 PM in the field
"End Time", and also would stamp 15 in the "Total Time" Field.

I have spent about 15 hours reading in the Microsoft help and I am as
ignorant on how to do this as before I begun.

Thanks for the help

manuel

[Jim Evans]

For the End Time field, look at the datePart() Function,
For the TotalTime field, look at the dateDiff() Function.

hth,

Jim Evans

"luscus" <LU****@NETZERO.NET> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I am trying to device a formula so that when i check of a yes/no box
(done) it will automatically add the time in a field called "End
Time" and at the same time stamp the amount of minutes between a field
"begining Time" ( which already automatically stamps the time as you
enter a new record) and the field "end time"

For example : If I begun the new record at 2:45 PM worked on the
problem untill 3:00 PM and solved it. I would then proceed to check of
the box in the field "Done" this would then stamp 3:00 PM in the field
"End Time", and also would stamp 15 in the "Total Time" Field.

I have spent about 15 hours reading in the Microsoft help and I am as
ignorant on how to do this as before I begun.

Thanks for the help

manuel