You are here:

Using MS Access/build a formula in access


I am a newbie with access. I am trying to build a formula for IBW (ideal body weight). It is based on a patients gender and their height in inches.
I would like to have a form called frmOncologyPatients where a patients gender and height in inches is entered and the results (sex, heightinches, IBW) go into the table, tblOncologyPatients. I believe the formula for IBW would be:
IBW: IIf([Sex]="Male",(50+(2.3*(HeightInches.tblOncologyPatients-60))),(45.5+(2.3*(HeightInches.tblOncologyPatients-60))))
which I have made a query for, qryIBW.
When I enter the gender and height into the form the data goes into the table but I can not get the IBW to go to the table. A parameter box comes up asking for the height again.
Is there a way for the height for this parameter box be pulled from the height that has already been entered into the form and then the result, IBW, will show up in the form and go into the table?

First, it would help to know what version of Access you are using.

But, there is NO reason to store the IBW. As a general rule we do not store calculated values. You can use the expression you create to display the value at any time.

There is, however, an error in your expression. you are referencing the height incorrectly. If used in a query, the expression should be:

IBW: IIf([Sex]="Male",(50+(2.3*(HeightInches-60))),(45.5+(2.3*(HeightInches-60))))

That's the reason it was prompting for the height, because it couldn't find a value named HeightInches.tblOncologyPatients. If your query included more than one table that has a field named HeightInches, then the correct reference is tablename.fieldname (i.e. tblOncologyPatients.HeightInches).

You do not, however, need to use a query here to display this value on your form. Just add a textbox and make its ControlCours:


This assumes that you have a control on your form named Sex and another named HeightInches. Also, that the control named Sex holds the values either Male or Female.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.