473,388 Members | 1,009 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,388 software developers and data experts.

calculating time II

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
3 2666
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Ron Adam | last post by:
Hi, I'm having fun learning Python and want to say thanks to everyone here for a great programming language. Below is my first Python program (not my first program) and I'd apreciate any...
0
by: Kasp | last post by:
Hi there, I am trying to make an OLAP cube on a table having two columns (datetime, Number_of_times_an_event_occured). My dimension is time and I want to measure the Min and Max times an event...
1
by: jlm | last post by:
I have a form which feeds table (TblEmpLeave) of Employee Leave Time (time taken off for Administrative, Annual, Sick, Compensation leave). I have EmpID, LeaveDate, LeaveType, LeaveHours fields on...
3
by: Miller | last post by:
Hi, Can someone tell me how to calculate MFLOPS for the following C# code (on a Pentium 4 2.0 Ghz)? for (i=0; i<n; i++) { for (j=0; j<n; j++) { for (k=0; k<n; k++)
12
by: jUrner | last post by:
Hello all I have the problem of how to calculate the resolution of the system clock. Its now two days of head sratching and still there is nothing more than these few lines on my huge white...
25
by: Umesh | last post by:
i want to calculate the time required to execute a program. Also i want to calcute the time remaining for the execution of the program. how can i do that? pl mention some good websites for...
25
by: Blaize | last post by:
Hi, I'm having an issue trying to calculate time. Its okay if the value does not exceed 24 hours otherwise I get a date and hours listed. For example, I have a loop which looks through a table...
10
freddieMaize
by: freddieMaize | last post by:
Hi there, I need to hit few list of sites from my application and find the response time of those site and sort them (which ever is opening first will sit at the top). I have done the same using...
3
by: mfaisalwarraich | last post by:
Hi everybody. I have some problem while calculating time. i have employees who are working on hourly basis. i want to calculate the time. for example if an employee start working at 11:00pm...
4
by: Missionary2008 | last post by:
Thank you in advance for your help. I'm trying to calculate the Total Time in hours and minutes to complete a job. The way we are calculating it is by taking the Fee paid and dividing that by a...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.