Calculating PCI BMI

[zar786]

In my Football League database I need to create a public function that takes a players weight, height, aage and gender and returns a players PCI BMI using the following calculation:

men: 0.5 * weight / height squared + 11.5
women: 0.4 * weight / height squared + 0.03 * age + 11

taken from www.halls.md

I have tried using the following code however i'm not sure if the code is correct and doesn't work

Public Function myCalculateBMI()
Dim intBMI As Integer
If gender = Male The=
intBMI = 0.5 * weight / height * height + 11.5
Else
intBMI = 0.4 * weight / height * height + 0.03 * age + 11
End If
myCalculateBMI = intBMI
End Function
End Sub

I have to display the output on a form

If anyone could help me out would really appreciate it, i also have to highlight players that have BMI's not in the normal range, not sure how to do this? I have to use VBA to do everything.

 

[pbaldy]

Two issues I see right off. First, unless the variables (age, gender...) are public variables populated prior to this, the function should accept them as arguments, like:

Public Function myCalculateBMI(Age as Integer, Weight As Integer...)

Second, you need to use parenthesis around the various components of the calculation, as appropriate. Don't rely on the default precedence. In other words,

A * B + C

will return a different result than

A * (B + C)

 

[Sergeant]

The function doesn't know you are talking about the form fields.
Off the top of my head:

Public Function MyCalculateBMI(intHeight As Integer, _
                                intWeight As Integer, _
                                Optional blnIsMale As Boolean = True, _
                                Optional intAge As Integer) as Integer
    If blnIsMale Then
        BMI = (0.5 * intWeight) / ((intHeight * intHeight) + 11.5)
    Else
        BMI = (0.4 * intWeight) / (((intHeight * intHeight) * intAge) + 11)
    End If
    MyCalculateBMI = BMI
End Function

You can then call it from anywhere in VBA by supplying the arguments.

 

[zar786]

thx for the code, i tried it but it's still not working, i think it may have something to do with the boolean male function, as the field were it says whether they are male or female is called gender, so i assume this must need to be in there somewhere otherwise it won't know were it's getting the information, i assume it knows were it's calling the rest of the information from as it's on the same form. I've used an unbound textbox on a form and called the calculateBMI module from it, i assume that's ok or do i need to do it another way? at the moment i get #Name? on the textbox it should be telling the BMI.

Thanks again for the help.

 

[Sergeant]

I'm not sure how you attempted to use the function, but you can do it right in the text box's control source...
=IIf([txtGender]="Male",(0.5*[txtWeight])/(([txtHeight]*[txtHeight])+11.5),(0.4*[txtWeight])/((([txtHeight]*[txtHeight])*[txtAge])+11))
or something like that.
You will naturally have to replace the references with your own control's names.

Just went and looked at the page you linked to, and you may have some problems with the way you interpreted the formula.
It should be BMI = Kg/M2 for males. Where did you get the +11.5?

 

[zar786]

i've managed to get something to come up, but it's not right, i've played around with the code but either get a really low number like 0.23 or something too high like 65 and sometimes the females calculation doesn't work, you missed out the + 0.03 on the female calculation i've added this, i assume it doesn't need brackets around it?

at the moment:

=IIf([txtGender]="Male",(0.5*[txtWeight])/(([txtHeight]*[txtHeight])+11.5),(0.4*[txtWeight])/((([txtHeight]*[txtHeight])+0.03*[txtAge])+11))

gives me the low numbers, i think it's to do with the brackets and were there placed, this seemes to change things, would really appreciate a hand.

 

[zar786]

finally got it working! using:

=IIf([txtGender]="Male",(0.5*[txtWeight])/([txtHeight]*[txtHeight])+11.5,(0.4*[txtWeight])/(([txtHeight]*[txtHeight])+0.03*[txtAge])+11)

thx for all your help!!

 

[zar786]

is there any way to limit the number of decimal places? 26.47856348756348756347 don't look very good lol

 

[zar786]

is there anyway to limit the number of decimal places? 24.348798376398 don't look good lol.

 

[Mile-O]

Use the Format property of the textbox. 0.0 should be ok.

 

[zar786]

thx, should of known it would be something simple