Calculating or making a formula work automatic

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

Guest

I have columns "Height" "Weight" and BMI on a table. I would like to enter
the data in a form. entering the first two numbers and automatic entry in
the "BMI" with the following formula (Weight / ( Height * Height) ) * 703.
What is the sintax for the formula? should it be done in the querry or the
form?
 
I have columns "Height" "Weight" and BMI on a table. I would like to enter
the data in a form. entering the first two numbers and automatic entry in
the "BMI" with the following formula (Weight / ( Height * Height) ) * 703.
What is the sintax for the formula? should it be done in the querry or the
form?
Click to expand...

Either, take your pick!

In a Query simply type

BMI: (Weight / ( Height * Height) ) * 703.

in a vacant Field cell in the query grid.

On a Form, create a textbox on the Form, view its properties, and set
its Control Source property to

=(Weight / ( Height * Height) ) * 703.

Note that BMI should NOT exist in your table. Just calculate it on
demand. Storing it in the table would be redundant, and would risk bad
data (say if you stored the BMI and then changed the weight, you'ld
then have an incorrect BMI in the table).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
thank you

How I do another formula or procedure to do the following:
take the amount on "Total Points" and type
'Excellent' >= (more or equal than) 90 or
'Good' < (less than) 89.9 but > (more than) 75
'Marginal' < 74.9 but > 70
'Poor' < 70
on a "Level" Text box all of this to be done in a form

Giovanni
 
thank you

How I do another formula or procedure to do the following:
take the amount on "Total Points" and type
'Excellent' >= (more or equal than) 90 or
'Good' < (less than) 89.9 but > (more than) 75
'Marginal' < 74.9 but > 70
'Poor' < 70
on a "Level" Text box all of this to be done in a form
Click to expand...

Take a look at the documentation for the Switch() function (open the
VBA editor by typing Ctrl-G to get to the right Help file).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Let see if I can do the syntax
Switch (>89.9 said:
69.9,Marginal][<69.9,Poor]
Click to expand...
if this is correct should I put it in the control source of the box?
Click to expand...

It's actually simpler than that, and you don't need the brackets. The
arguments are taken pairwise left to right; when it finds a pair for
which the first argument is TRUE, it returns the second argument of
the pair and quits. So setting the Control Source to

=Switch([Score] > 89.9, "Excellent", [Score] > 75, "Good", [Score] >
69.9, "Marginal", True, "Poor")

should do what you want.

You might also want to consider setting up a translation table; with
only five values it's marginal whether you will get into trouble
embedding the translation from score to label in some non-obvious
code, vs. having a little five-row Table which can be used in a query.
With the table you can easily change the labels or the cutoff scores
without needing to change your code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Everything looks great in the form, BUT how do I put all the information in a
report with all the records and all the needed colums. I did all the
formulas in the form. You only can make a report from a querry or a table.
When I tried to do the fomulas in the querry for the form nothing was coming
up so I decide to do everything in the form.

John Vinson said:
Let see if I can do the syntax
Switch (>89.9 said:
69.9,Marginal][<69.9,Poor]
Click to expand...
if this is correct should I put it in the control source of the box?
Click to expand...

It's actually simpler than that, and you don't need the brackets. The
arguments are taken pairwise left to right; when it finds a pair for
which the first argument is TRUE, it returns the second argument of
the pair and quits. So setting the Control Source to

=Switch([Score] > 89.9, "Excellent", [Score] > 75, "Good", [Score] >
69.9, "Marginal", True, "Poor")

should do what you want.

You might also want to consider setting up a translation table; with
only five values it's marginal whether you will get into trouble
embedding the translation from score to label in some non-obvious
code, vs. having a little five-row Table which can be used in a query.
With the table you can easily change the labels or the cutoff scores
without needing to change your code.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Everything looks great in the form, BUT how do I put all the information in a
report with all the records and all the needed colums. I did all the
formulas in the form. You only can make a report from a querry or a table.
When I tried to do the fomulas in the querry for the form nothing was coming
up so I decide to do everything in the form.
Click to expand...

You can put exactly the same expressions as the control source of a
Report textbox as you're using in the Form textbox; or, you can put
the expression in a vacant Field cell in a Query. Don't use the = sign
that you need for a textbox control source; instead, precede the
expression with a new (not duplicating any existing) fieldname and a
colon. For example:

Grade: Switch([Score] > 89.9, "Excellent", <etc>


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Done!
Thank you for taking the time with me. I am a TSgt with the Air Force
Reserves and I was task to keep this database going from one of my retired
colleagues, thank you again.

I did all the changes on a copy of the database I took home. How I import
all the changes to the copy on the office network without having to redo them
manually?

John Vinson said:
Everything looks great in the form, BUT how do I put all the information in a
report with all the records and all the needed colums. I did all the
formulas in the form. You only can make a report from a querry or a table.
When I tried to do the fomulas in the querry for the form nothing was coming
up so I decide to do everything in the form.
Click to expand...

You can put exactly the same expressions as the control source of a
Report textbox as you're using in the Form textbox; or, you can put
the expression in a vacant Field cell in a Query. Don't use the = sign
that you need for a textbox control source; instead, precede the
expression with a new (not duplicating any existing) fieldname and a
colon. For example:

Grade: Switch([Score] > 89.9, "Excellent", <etc>


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I did all the changes on a copy of the database I took home. How I import
all the changes to the copy on the office network without having to redo them
manually?
Click to expand...

You can use File... Get External Data... Import to import any object
(a Form, Query, Table, or whatever) from a separate database. Rename
the current form first just in case something breaks; be sure to
IMMEDIATELY type Ctrl-G and select Debug... Compile <your database>
first thing.

Ideally most databases, and ANY multiuser databases, should be "split"
into a "backend" containing the tables, and a "frontend" linked to the
backend tables, containing the forms, queries, etc. With this design
you can just delete the user's entire database (it doesn't contain any
of their data!) and replace it with a new one.


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top