Text Box Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello there;

I have four fields that I need to have summed up, and then the value entered
into a fifth box. The formula that I need to have working within my form is
as follows:
(Q1 + Q2 + Q3 + Q4) / 4

The Q#'s are the field names that I have given the fields, and I was
wondering the easiest way to get this calculation entered into a fifth text
box labeled "QuestAvg".

I'm willing to use VB, however, I am not sure how to get this working.

Thank you
 
1. Create a query that uses this table.

2. In query design view, type this expression into a fresh column in the
Field row:
(Nz([Q1],0) + Nz([Q2],0) + Nz([Q3],0) + Nz([Q4],0)) / 4

Note that you do not store calculated totals in a database. More info on
that:
Calculated fields
at:
http://allenbrowne.com/casu-14.html
 
Just add your text box and type your formula in it (preceded by an "=")

=([Q1] + [Q2] + [Q3] +[Q4)/4
 
dakoris73 said:
Hello there;

I have four fields that I need to have summed up, and then the value
entered into a fifth box. The formula that I need to have working
within my form is as follows:
(Q1 + Q2 + Q3 + Q4) / 4

The Q#'s are the field names that I have given the fields, and I was
wondering the easiest way to get this calculation entered into a
fifth text box labeled "QuestAvg".

I'm willing to use VB, however, I am not sure how to get this working.

Thank you
Click to expand...

The ControlSource of the fifth TextBox would be...

=Nz(Q1,0) + Nz(Q2,0) + Nz(Q3,0) + Nz(Q4,0)

The Nz() handles the possibility that the field might contain a Null value.
Without the Nz() functions any one of the values being Null would produce a
Null for the sum.

This value will be displayed on the form only and not stored (nor should it
be).
 
Hello all;

Thank you for your assistance with this issue. I have tried all the
suggestions, yet have not been able to get the field to give me the avg that
I am looking for. So I'm guessing that I am not understanding fully how to
use these options, or where to place them on the form or code to be able to
get this to work. I like the idea of being able to use the Query feature to
make this calculation to work, however, I am not sure where exactly to enter
the calculation, or how to get that particular field to display the
calculated event.

Please help me to see exactly where to put that query feature in, and also
how or where I can get that field to access that particular query.

Thanks again in advance.

Mikey
dakoris73
 
In your form's property box, click the DATA tab. In the RECORD SOURCE
field, you will see a builder button to the right [...].

Click that and it should open your query in design-view.

In a new column, enter one of the formulas provided.

It would be something like...

NewAverage: (Nz([Q1],0) + Nz([Q2],0) + Nz([Q3],0) + Nz(Q4],0))/4

Then close that query and save it.

In the form's field list, you should now have a field called "NewAverage".
Simply drag that onto your form.
 
Hello Rick B;

Thank you for your response with this particular request. However, when I do
as you instructed, I don't see the RECORD SOURCE field, but instead I see a
CONTROL SOURCE with the builder button. I tried that, and managed to save the
query, however, I still can't get this to work on the form. Its wanting to
create a subform instead.

Any other thoughts? I am using Access 2003 using a standard wizard based
form. but I can't get the average to appear in this one particular box.......

Thank you again.....

Rick B said:
In your form's property box, click the DATA tab. In the RECORD SOURCE
field, you will see a builder button to the right [...].

Click that and it should open your query in design-view.

In a new column, enter one of the formulas provided.

It would be something like...

NewAverage: (Nz([Q1],0) + Nz([Q2],0) + Nz([Q3],0) + Nz(Q4],0))/4

Then close that query and save it.

In the form's field list, you should now have a field called "NewAverage".
Simply drag that onto your form.


--
Rick B



dakoris73 said:
Hello all;

Thank you for your assistance with this issue. I have tried all the
suggestions, yet have not been able to get the field to give me the avg
that
I am looking for. So I'm guessing that I am not understanding fully how to
use these options, or where to place them on the form or code to be able
to
get this to work. I like the idea of being able to use the Query feature
to
make this calculation to work, however, I am not sure where exactly to
enter
the calculation, or how to get that particular field to display the
calculated event.

Please help me to see exactly where to put that query feature in, and also
how or where I can get that field to access that particular query.

Thanks again in advance.

Mikey
dakoris73
Click to expand...
Click to expand...
 
When you run the query does it show the average?


--
Rick B



dakoris73 said:
Hello Rick B;

Thank you for your response with this particular request. However, when I
do
as you instructed, I don't see the RECORD SOURCE field, but instead I see
a
CONTROL SOURCE with the builder button. I tried that, and managed to save
the
query, however, I still can't get this to work on the form. Its wanting to
create a subform instead.

Any other thoughts? I am using Access 2003 using a standard wizard based
form. but I can't get the average to appear in this one particular
box.......

Thank you again.....

Rick B said:
In your form's property box, click the DATA tab. In the RECORD SOURCE
field, you will see a builder button to the right [...].

Click that and it should open your query in design-view.

In a new column, enter one of the formulas provided.

It would be something like...

NewAverage: (Nz([Q1],0) + Nz([Q2],0) + Nz([Q3],0) + Nz(Q4],0))/4

Then close that query and save it.

In the form's field list, you should now have a field called
"NewAverage".
Simply drag that onto your form.


--
Rick B



dakoris73 said:
Hello all;

Thank you for your assistance with this issue. I have tried all the
suggestions, yet have not been able to get the field to give me the avg
that
I am looking for. So I'm guessing that I am not understanding fully how
to
use these options, or where to place them on the form or code to be
able
to
get this to work. I like the idea of being able to use the Query
feature
to
make this calculation to work, however, I am not sure where exactly to
enter
the calculation, or how to get that particular field to display the
calculated event.

Please help me to see exactly where to put that query feature in, and
also
how or where I can get that field to access that particular query.

Thanks again in advance.

Mikey
dakoris73

:

Hello there;

I have four fields that I need to have summed up, and then the value
entered
into a fifth box. The formula that I need to have working within my
form
is
as follows:
(Q1 + Q2 + Q3 + Q4) / 4

The Q#'s are the field names that I have given the fields, and I was
wondering the easiest way to get this calculation entered into a fifth
text
box labeled "QuestAvg".

I'm willing to use VB, however, I am not sure how to get this working.

Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top