IIf - Two Questions - And I'm Stumped 1

[killer23]

I am creating a training database and am having a bit of trouble.

Issue #1 - I have a series of fields in my form as follows:

Age Gender Run_M Run_S Pull-ups Flex Crunches Score Class

Each field calculates a score. The combined scores translate into a class (1st, 2nd, 3rd, fail) depending on age, gender, and score. I have created a nested IIf formula - that works if the gender selected is "Male" only. I am now at the point in which the formula is too long for acces to calculate.

How do I "tell" the formula to switch calculation types based on Gender, then age, then score? Here is what has worked thus far.

=IIf([Gender]="male" And [Age]>=27 and [Score]>=225,"1st Class")

This formula quickly grows as each age and class are added.

Issue #2

On this same form I need to calculate the body fat percentage based on DOD standards for either Male or Females. All measurements are taken in inches (i.e. 17" neck and 35.5" waiste).

For Males the formula is
%Body Fat = 86.01*Log10(Waiste-Neck)-70.041*Log10(Height)-36.76

For Females the formula is
%Body Fat = 163.205*Log10(Waiste+Hips-Neck)-97.684*Log10(Height)-78.387

The fields I have for this are

Height Weight Waiste Neck Hips

I am not a programmer - more of a LAN/WAN guy - so any help or guidance you can provide would be greatly appreciated.

Thanks in Advance -

Killer23

 

[Remou]

Tables are generally easier to maintain than code, I think, especially for your users.

 

[killer23]

I'm feeling very much like a "rock" here. Perhaps I'm missing something very basic. Do "Public Function" codes go into "Modules" or somewhere else to run. I've tried each sample offered and can create modules, place that module into the appropriate "Control Source" on my form and get - - nothing, saved for the dreaded #Name? Would I be better off creating a Query - if so how do I add code to such a Query?

Sorry for being so dense.

Thanks,
Killer23

 

[PHV]

Go to VBE (Alt+F11), menu Insert -> Module
Copy'n'Paste the code I posted.
Now access will not choke on your formulas with Log10 calls.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

killer23 . . .

Wow . . . how this thread has grown. For [blue]Issue #1[/blue], try this function:

[blue]Public Function ScoreClass([purple][b]Age[/b][/purple] As Long, [purple][b]Score[/b][/purple] As Long) As String
   Dim Ary, Rank, n As Long
   
   ScoreClass = "Fail"
   Rank = Split("1st Class;2nd Class;3rd Class;", ";")
   
   If Age <= 26 Then
      Ary = Split("225;200;135", ";")
   ElseIf Age <= 39 Then
      Ary = Split("200;150;110", ";")
   ElseIf Age <= 45 Then
      Ary = Split("175;125;088", ";")
   Else
      Ary = Split("150;100;065", ";")
   End If
   
   For n = LBound(Ary) To UBound(Ary)
      If Score >= Ary(n) Then
         ScoreClass = Rank(n)
         Exit For
      End If
   Next

End Function[/blue]

See Ya! . . . . . .

 

[MajP]

Killer23,
This function works

Public Function fncBodyFat(theNeck As Single, theWaist As Single, theHips As Single, theHeight As Single, theGender As String) As Integer
  If theGender = "Female" Then
    fncBodyFat = 163.205 * Log(theWaist + theHips - theNeck) / Log(10#) - 97.684 * Log(theHeight) / Log(10) - 78.387
    Debug.Print theGender
 Else
    fncBodyFat = 86.01 * Log(theWaist - theNeck) / Log(10) - 70.041 * Log(theHeight) / Log(10) + 36.76
  End If
End Function

you can test it with

Public Sub testBodyFat()
  Debug.Print fncBodyFat(16, 32, 0, 69, "Male")
  Debug.Print fncBodyFat(14, 32, 36, 60, "Female")
End Sub

Your formula for males was wrong it is + 36.76. My values match with the tables.

Now how to use it. First drop this into a regular module, not a class module along with the code for PFT class. You now can use this function in a query or unbound control. I would probably put them into a query so that you can use them on multiple reports and forms easily. The query needs to include all the relevant fields. So for body fat:
Waist
Height
Gender
Hips
Neck
To do a calculated field. Give the field a Name followed by a colon then the formula. The formula uses the field names as the arguments. Ex

BodyFat%: fncBodyFat([tblPFT.Neck],[tblPFT.Waist],[tblPFT.Hips],[tblPFT.Height],[tblPFT.Gender])

(this assumes the the table name is "tblPFT" and the fields are named Waist, Height, Gender...)
Now on any form or report that is bound to this query you should be able to use the field "BodyFat%" just like any other field.

 

[killer23]

Alright - finally getting somewhere here. Thanks to MajP I have the Body Fat working in a query. Now I am stuck when I try to get my query to work within a form - PFT_History.

The idea is that a clerk will fill in the required "raw" data (i.e. # of pull-ups, crunches, etc ...) and the query will then calculate the combined scores as discussed above.

When I bind the query to a text box all I get is #Name? or if I create a form from the query I cannot perform data entry.

Thanks to all (once again) and thanks for your patience.

S/F
Killer23

 

[MajP]

Killer23,
I have a training DB that I got from someone. I did not develop it, and it is not the greatest design. But, the front end works well and it has a lot of capability. There is probably a million more of these things out there. This one is 5 megs. Are you in the Global, and can you recieve a file that large?
For your current problem. Look at your query. Go to the bottom of the query and see if there is new line for entering data. If there is no way to enter a new record, tell me what tables are in your query and how are they joined. There are many possibilities to make a query not updateable depending on joins, and criteria.
If this query is updateable and the function works, then the form based on this query should work. Check the allow additions, allow deletions, allow edit properties of the form.
You said "bind a query to a textbox". Not sure if this is what you really meant to say. Normally, Forms are bound to recordsets and controls are bound to fields from that recordset. So you should have the calculated field in the query that the form is based on. Then the control is bound to the field just like any other fields control source.

 

[killer23]

MajP -

It all works now - I was being "thick". Thanks so much for your expert help - here's a star and a Commedation.

Regards,
S/F
Killer23

 

[MajP]

Glad to help. I am at MCCDC in Quantico. I am a 7202 doing analysis up here.