By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,306 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,306 IT Pros & Developers. It's quick & easy.

calculating time II

P: n/a
Thanks for all the responses on my first question. Unfortunately the
answers I was given were too complicated for my small brain , and
neophite condition to understand. So if you could talk down to me and
write in easier terms I would be very gratefull to all you access
wizards!

Here is my problem. I have a table with maybe 10 fields, It is used to
imput information taken over the phone to solve patient problems in our
Spanish Department. So it has Fields like: Date, Time, Patient Name,
Provider Name, Comments. Now here is where I need help.

At the End of the table I have the following Fields: End Time, Total
Time, Done. I was thinking that once I have finished solving a patients
problem (could be 5 minutes, or 20 ) I would check a yes/no box (Done)
and that would set off two orders;
1- to tell the End Field to stamp the time at that moment
2- to Tell the Total Time field How many minutes I spent on that one
entry.

I got 5 different answers and I understood none of them, becouse I am
very green at Access:

I got one that said:
In the On Change event of the "Done" Checkbox (Whhhhhaaaaat? I looked
everywhere for the On Change event never found it)
Create an unbound text box on your form ( I am not working with a form,
and I have no Idea what an unbound text box is)

I tried inserting Me![End Time}=Now() in the Default value,and then in
Validation rule, of that field but access said No,no,no can't do
that???? why?

I Know that being such a neophite makes it difficult to help, but if
you could I would apreciate simple answers and if you use terms like On
change, tell me where to find it, or send me a print screen of where it
is. at the above email or to ma******@BIDMC.HARVARD.EDU

Thanks a lot to all.

Manuel

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 16 Feb 2005 05:46:01 -0800, luscus wrote:
Thanks for all the responses on my first question. Unfortunately the
answers I was given were too complicated for my small brain , and
neophite condition to understand. So if you could talk down to me and
write in easier terms I would be very gratefull to all you access
wizards!

Here is my problem. I have a table with maybe 10 fields, It is used to
imput information taken over the phone to solve patient problems in our
Spanish Department. So it has Fields like: Date, Time, Patient Name,
Provider Name, Comments. Now here is where I need help.

At the End of the table I have the following Fields: End Time, Total
Time, Done. I was thinking that once I have finished solving a patients
problem (could be 5 minutes, or 20 ) I would check a yes/no box (Done)
and that would set off two orders;
1- to tell the End Field to stamp the time at that moment
2- to Tell the Total Time field How many minutes I spent on that one
entry.

I got 5 different answers and I understood none of them, becouse I am
very green at Access:

I got one that said:
In the On Change event of the "Done" Checkbox (Whhhhhaaaaat? I looked
everywhere for the On Change event never found it)
Create an unbound text box on your form ( I am not working with a form,
and I have no Idea what an unbound text box is)

I tried inserting Me![End Time}=Now() in the Default value,and then in
Validation rule, of that field but access said No,no,no can't do
that???? why?

I Know that being such a neophite makes it difficult to help, but if
you could I would apreciate simple answers and if you use terms like On
change, tell me where to find it, or send me a print screen of where it
is. at the above email or to ma******@BIDMC.HARVARD.EDU

Thanks a lot to all.

Manuel


regarding .. ( I am not working with a form, and I have no Idea what
an unbound text box is)

As long as you claim to be a neophyte in Database design, I'll give
you your most important Access design lesson.
Stop right now.
You can not do what you want unless you create a Form for data entry,
and stop using the table for anything other than data storage.

Access is not Excel. You cannot do calculations in a table field.
Tables do not have events, Forms do.

Create a form using your table as the Form record source. Then you
might be able to use the methods noted in the replies to your original
post.

Further, if you do really have fields named "Date" and "Time" I would
strongly suggest you change those names now, before you go any
further.

Date and Time are reserved Access/VBA/Jet words and should not be used
as a field name.
See the Microsoft KnowledgeBase article for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'
Good luck.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #2

P: n/a
luscus wrote:
Thanks for all the responses on my first question. Unfortunately the
answers I was given were too complicated for my small brain , and
neophite condition to understand. So if you could talk down to me and
write in easier terms I would be very gratefull to all you access
wizards!

Here is my problem. I have a table with maybe 10 fields, It is used to
imput information taken over the phone to solve patient problems in our
Spanish Department. So it has Fields like: Date, Time, Patient Name,
Provider Name, Comments. Now here is where I need help.

At the End of the table I have the following Fields: End Time, Total
Time, Done. I was thinking that once I have finished solving a patients
problem (could be 5 minutes, or 20 ) I would check a yes/no box (Done)
and that would set off two orders;
1- to tell the End Field to stamp the time at that moment
2- to Tell the Total Time field How many minutes I spent on that one
entry.

I got 5 different answers and I understood none of them, becouse I am
very green at Access:

I got one that said:
In the On Change event of the "Done" Checkbox (Whhhhhaaaaat? I looked
everywhere for the On Change event never found it)
Create an unbound text box on your form ( I am not working with a form,
and I have no Idea what an unbound text box is)

I tried inserting Me![End Time}=Now() in the Default value,and then in
Validation rule, of that field but access said No,no,no can't do
that???? why?

I Know that being such a neophite makes it difficult to help, but if
you could I would apreciate simple answers and if you use terms like On
change, tell me where to find it, or send me a print screen of where it
is. at the above email or to ma******@BIDMC.HARVARD.EDU

Thanks a lot to all.

Manuel

This is what Tom said"
--------
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)
-------

1. Click on the control for the checkbox Done. A popup menu will open.
2. Select Property. this will open a property box.
3. select the event "AfterUpdate" and click the ... to the right
of the afterupdate entry. This will open a VBA window that
will allow you to enter VBA code.
4. Enter the following replacing the name Tom used with your name
for the end time.
txtEndTime = Time
Replace it with Me![End Time} = Time
Each time the checkbox is changed, this code will run.
5. Then add the following to the same event
TotalTimeInMinutes: DateDiff("n",BeginTime, EndTime)
Replace the name with:
Me![Total Time]= DateDiff("n",Me![Begin Time], Me![End Time])
6. When completed the code should look like this.

Me![End Time} = Time
Me![Total Time]= DateDiff("n",Me![Begin Time], Me![End Time])

What Tom said about a calculated fields in the database is good
advice. Go ahead and do what you want to do so you can learn
how the afterupdate event and other events work. Then when you
get an error in your database you will learn or understand why
you should not put calculated fields in your table.

Example: If you or others review the records you have created
and manually change the start time or end time , then your
calculated time difference in Total Time will be wrong.
This can be done by opening the table is datasheet and changing
the values.

The total time can be calculated in a query for other forms or
reports to use. Then if some one changes the start and/or end time
it will still calculate the correct total time.

In this form, total time should be an unbound control. That means
it should not be linked to a field in the table the form is linked
to. It the OnCurrent record of the form you can add the following code
to recalculate the total time of existing records.

Me![Total Time]= DateDiff("n",Me![Begin Time], Me![End Time])

You will have to add error checking to your code to check for
the following.
1. The checkbox is ture or done.
2. Start time is a valid time.
3. End time is a valid time.
4. End time is not before start time unless time was was from the
day before.
Hope this helps.

Ron

--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com

Nov 13 '05 #3

P: n/a
Thanks for the Advice, I will follow it.

Tell me this, if I make a form based on the table, and add to the form
these "end time, and total time, will it record those in the table as
well? (as long as I have made those fields in the table?)

you see I need the total time to find out at the end of the month how
long is the average problem last.

Manuel

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.