Forms time calculation

[Gabriella777_2]

I am attempting to create a calculation in a form that will still enter it
into the main table.
I am attempting to calculate total hours of down time from entries in the DT
Months, DT Weeks, DT Days, DT Hours, DT Minutes entry boxes.
My calculation might look something like this:
Down Time=(((DT Months*21)*24)+((DT Weeks*5)*24)+(DT Days*24)+DT Hours+(DT
Minutes/60))

I would like it to display in the form as well as the table the form updates.

I am a learn by trial and error and asking questions novice at MS Access and
would like to get as much help as I can, please.

Thanks to anyone who can help.

 

[John W. Vinson]

I am attempting to create a calculation in a form that will still enter it
into the main table.

Could you explain WHY?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

It MIGHT be appropriate to do this in some very specific cases, and it can be
done... but I hesitate to post the details unless you're really sure you are
on the right track.

 

[Gabriella777_2]

You asked why I want to create the calculation and still enter that in the
table. . .
-The gentleman I am creating this database for needs to be able to calculate
at any given time how much time was spent doing various projects - quickly
and easily.
He bases his work on those calculations.
I am also trying to make it simplified and speedy to enter the data by
himself or anyone else with little or no training.
Currently he is using MS Excel to enter the data - how he is totaling
everything I don't know. He creates a new spreadsheet every year and the one
that I completed for him for 2007 had over 1300 entries in it with very
little to differentiate between tasks.
Additionally, when he enters the time the equipment is down/being serviced
he has to quickly figure out or remember the decimals for fractions of hours
- his memory was inacurate. This will create more accuracy with time
listings.

If there is something else that you could suggest - I would appreciate it.
(However, I cannot download anything from the internet or purchase new
software.)

Again, thanks for your assistance. I hope to hear from you soon.

 

[John W. Vinson]

You asked why I want to create the calculation and still enter that in the
table. . .
-The gentleman I am creating this database for needs to be able to calculate
at any given time how much time was spent doing various projects - quickly
and easily.

Exactly. That's just what Access is for.

He bases his work on those calculations.
I am also trying to make it simplified and speedy to enter the data by
himself or anyone else with little or no training.

That's why I suggest that you store the base data, and use a Query to do the
calculation. Doing so will ensure that the calculation is in fact giving the
correct result. If you store the result of the calculation in a Table, that
value can be edited, or the underlying data can be edited - making the value
that he's using WRONG. You surely don't want to set up an application which is
pretty much guaranteed to return wrong values, do you!?

Currently he is using MS Excel to enter the data - how he is totaling
everything I don't know. He creates a new spreadsheet every year and the one
that I completed for him for 2007 had over 1300 entries in it with very
little to differentiate between tasks.
Additionally, when he enters the time the equipment is down/being serviced
he has to quickly figure out or remember the decimals for fractions of hours
- his memory was inacurate. This will create more accuracy with time
listings.

If there is something else that you could suggest - I would appreciate it.
(However, I cannot download anything from the internet or purchase new
software.)

Again:

Store the base data.

Use Queries to do the calculations.

Base Forms or Reports on those queries, so that the user can see the results
of the calculations, done fresh on the actual stored data, as of the moment
that the query is run.

 

[Gabriella777_2]

Okay - so how do I get him to enter the proper decimal to begin with?
Do I create months, weeks, days. . . .columns and then create the query that
will calculate it or is there a way to create a "calculator" with in the form
so that he has to enter the correct information in the entry box?

 

[John W. Vinson]

Okay - so how do I get him to enter the proper decimal to begin with?
Do I create months, weeks, days. . . .columns and then create the query that
will calculate it or is there a way to create a "calculator" with in the form
so that he has to enter the correct information in the entry box?

Please explain the nature of the data and the nature of the calculation. You
say

Down Time=(((DT Months*21)*24)+((DT Weeks*5)*24)+(DT Days*24)+DT Hours+(DT
Minutes/60))

but I don't understand the nature or the meaning of these names. Is [DT
Months] the name of a textbox on a form? If so why the 21 - does every month
have 21 days? Can the user enter any number in any one of these controls?

Just note that if you (unwisely in my opinion) use blanks in the names of
controls or fields, you MUST enclose those names in [square brackets].

 

[Gabriella777_2]

Oops! Sorry! : )
Yes the words are text box labels and should have had brackets in them.
Additionally, yes the text boxes would allow entry as with any other portion
of the form.
The "21" is just an approximate networkdays in any given month (most have
20-22 exclusive of holidays).

--
Thanks and God bless you and yours,
Gabriella777_2

Okay - so how do I get him to enter the proper decimal to begin with?
Do I create months, weeks, days. . . .columns and then create the query that
will calculate it or is there a way to create a "calculator" with in the form
so that he has to enter the correct information in the entry box?

Please explain the nature of the data and the nature of the calculation. You
say

Down Time=(((DT Months*21)*24)+((DT Weeks*5)*24)+(DT Days*24)+DT Hours+(DT
Minutes/60))

but I don't understand the nature or the meaning of these names. Is [DT
Months] the name of a textbox on a form? If so why the 21 - does every month
have 21 days? Can the user enter any number in any one of these controls?

Just note that if you (unwisely in my opinion) use blanks in the names of
controls or fields, you MUST enclose those names in [square brackets].

 

[Gabriella777_2]

Is there a way to create a seperate "form" or "table" that would/could be
opened to use for entering the necessary numbers - click a "Convert to Hours"
button and it gives you the whole number plus decimal that the individual
would use to enter into the data entry form?

--
Thanks and God bless you and yours,
Gabriella777_2

Okay - so how do I get him to enter the proper decimal to begin with?
Do I create months, weeks, days. . . .columns and then create the query that
will calculate it or is there a way to create a "calculator" with in the form
so that he has to enter the correct information in the entry box?

Please explain the nature of the data and the nature of the calculation. You
say

Down Time=(((DT Months*21)*24)+((DT Weeks*5)*24)+(DT Days*24)+DT Hours+(DT
Minutes/60))

but I don't understand the nature or the meaning of these names. Is [DT
Months] the name of a textbox on a form? If so why the 21 - does every month
have 21 days? Can the user enter any number in any one of these controls?

Just note that if you (unwisely in my opinion) use blanks in the names of
controls or fields, you MUST enclose those names in [square brackets].

 

[John W. Vinson]

Is there a way to create a seperate "form" or "table" that would/could be
opened to use for entering the necessary numbers - click a "Convert to Hours"
button and it gives you the whole number plus decimal that the individual
would use to enter into the data entry form?

Sure. You could have a textbox for each unit of time with an expression as the
control source of a textbox

=NZ([DT Months])*21*24+NZ([DT Weeks])*5*24+NZ([(DT Days])*24++NZ([DT
Hours])+NZ([DT Minutes])/60.

Or better, to avoid retyping, you could put a command button on the form
storing the same expression into a bound control:

Private Sub cmdCalcHours_Click()
Me!txtOTHours = NZ(Me![DT Months])*21*24+NZ(Me![DT Weeks])*5*24+ _
NZ(Me![(DT Days])*24+NZ(Me![DT Hours])+NZ(Me![DT Minutes])/60.
End Sub

The NZ() functions (Null To Zero) will treat any blank controls as zero;
without them, any NULL in the expression will make the entire result NULL.

 

[Gabriella777_2]

Okay, first, I want to say thanks for hanging in there with me!
This has been the best experience I have had on this site so far! Thank
you! : )

Second, what is . . .?
Me!txtOTHours


Lastly, I have not had a successful command button to date and so far this
is no exception.
Can and/or would you, please, walk me through so that I may see what I am
doing wrong?

So far I have the necessary entry text boxes (ie. Month, Week, Day . . .)
and have a command button with the equation you gave me as the OnClick [Event
Procedure].

I do not know how to display the results.
Also, in the test run, I was given a lengthy error message.
So now what do I do?
Help, please! : ) Thank you very much again for your time and patience.

--
Thanks and God bless you and yours,
Gabriella777_2

Is there a way to create a seperate "form" or "table" that would/could be
opened to use for entering the necessary numbers - click a "Convert to Hours"
button and it gives you the whole number plus decimal that the individual
would use to enter into the data entry form?

Sure. You could have a textbox for each unit of time with an expression as the
control source of a textbox

=NZ([DT Months])*21*24+NZ([DT Weeks])*5*24+NZ([(DT Days])*24++NZ([DT
Hours])+NZ([DT Minutes])/60.

Or better, to avoid retyping, you could put a command button on the form
storing the same expression into a bound control:

Private Sub cmdCalcHours_Click()
Me!txtOTHours = NZ(Me![DT Months])*21*24+NZ(Me![DT Weeks])*5*24+ _
NZ(Me![(DT Days])*24+NZ(Me![DT Hours])+NZ(Me![DT Minutes])/60.
End Sub

The NZ() functions (Null To Zero) will treat any blank controls as zero;
without them, any NULL in the expression will make the entire result NULL.

 

[John W. Vinson]

Okay, first, I want to say thanks for hanging in there with me!
This has been the best experience I have had on this site so far! Thank
you! : )

Second, what is . . .?
Me!txtOTHours

It's what I was assuming would be the name of a Textbox on the form bound to
the OTHours field in your table.

Lastly, I have not had a successful command button to date and so far this
is no exception.
Can and/or would you, please, walk me through so that I may see what I am
doing wrong?

Not without more information about your form.

So far I have the necessary entry text boxes (ie. Month, Week, Day . . .)

What are the names - view the properties of the form and then of each of these
textboxes and tell me the Name property? While you're at it be sure that the
Control Source property is blank.

and have a command button with the equation you gave me as the OnClick [Event
Procedure].

The procedure that I posted (as AN EXAMPLE, a starting point, not finished
code adapted to your database) will do the following:

Private Sub cmdCalcHours_Click()
Me!txtOTHours = NZ(Me![DT Months])*21*24+NZ(Me![DT Weeks])*5*24+ _
NZ(Me![(DT Days])*24+NZ(Me![DT Hours])+NZ(Me![DT Minutes])/60.
End Sub

It will look on the current form for values in the five textboxes with Name
properties DT Months, DT Weeks, DT Days, DT Hours, and DT Minutes. It will use
your algorithm to calculate a single numeric value in hours and fractions of
an hour, and put that value into a control on the same form named txtOTHours.
If you don't like that name, change it to whatever name you choose - but it
should match the name of the control on the form in which you wish to store
the summed overtime hours.

I do not know how to display the results.

It *will* display the results, in the textbox named txtOTHours (or whatever
control you choose to put it in).

Also, in the test run, I was given a lengthy error message.
So now what do I do?

Post at least enough of the error message that I or another volunteer might be
able to figure it out.

Help, please! : ) Thank you very much again for your time and patience.

Sorry I'm not explaining things well enough - we'll get there though!

 

[Gabriella777_2]

Is there a regular e-mail that I could contact you at tomorrow with a "print
screen" pix of the form as is and we can discuss in more a real time status
rather than dragging this out to 2 months or so?
I need to be able to ask the simple questions that will connect everything,
mentally, for me as we discuss the process.
(I apologize for the drag out of this, etc.
I had intended to attend a local library free class but ended up taking my
daughter to the dentist instead.)
Thanks again for your time, instruction and assistance . . .I appreciate it
more than you know.
--
Thanks and God bless you and yours,
Gabriella777_2

Okay, first, I want to say thanks for hanging in there with me!
This has been the best experience I have had on this site so far! Thank
you! : )

Second, what is . . .?
Me!txtOTHours

It's what I was assuming would be the name of a Textbox on the form bound to
the OTHours field in your table.

Lastly, I have not had a successful command button to date and so far this
is no exception.
Can and/or would you, please, walk me through so that I may see what I am
doing wrong?

Not without more information about your form.

So far I have the necessary entry text boxes (ie. Month, Week, Day . . .)

What are the names - view the properties of the form and then of each of these
textboxes and tell me the Name property? While you're at it be sure that the
Control Source property is blank.

and have a command button with the equation you gave me as the OnClick [Event
Procedure].

The procedure that I posted (as AN EXAMPLE, a starting point, not finished
code adapted to your database) will do the following:

Private Sub cmdCalcHours_Click()
Me!txtOTHours = NZ(Me![DT Months])*21*24+NZ(Me![DT Weeks])*5*24+ _
NZ(Me![(DT Days])*24+NZ(Me![DT Hours])+NZ(Me![DT Minutes])/60.
End Sub

It will look on the current form for values in the five textboxes with Name
properties DT Months, DT Weeks, DT Days, DT Hours, and DT Minutes. It will use
your algorithm to calculate a single numeric value in hours and fractions of
an hour, and put that value into a control on the same form named txtOTHours.
If you don't like that name, change it to whatever name you choose - but it
should match the name of the control on the form in which you wish to store
the summed overtime hours.

I do not know how to display the results.

It *will* display the results, in the textbox named txtOTHours (or whatever
control you choose to put it in).

Also, in the test run, I was given a lengthy error message.
So now what do I do?

Post at least enough of the error message that I or another volunteer might be
able to figure it out.

Help, please! : ) Thank you very much again for your time and patience.

Sorry I'm not explaining things well enough - we'll get there though!

 

[John W. Vinson]

Is there a regular e-mail that I could contact you at tomorrow with a "print
screen" pix of the form as is and we can discuss in more a real time status
rather than dragging this out to 2 months or so?
I need to be able to ask the simple questions that will connect everything,
mentally, for me as we discuss the process.
(I apologize for the drag out of this, etc.
I had intended to attend a local library free class but ended up taking my
daughter to the dentist instead.)
Thanks again for your time, instruction and assistance . . .I appreciate it
more than you know.

Well... this goes a bit beyond what's typical for unpaid volunteer service,
but you could email me at jvinson <at> wysard of info <dot> com (edit out the
blanks and edit in the punctuation). What I'd suggest is using my GoToMeeting
account so I can actually see what's on your screen (I'll explain the details
by email or on the phone).

Have you looked at any of these tutorials?

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials