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]

For the score, how about a DlookUp and a table?

Gender AgeLower AgeUpper ScoreLower ScoreUpper Class

=DLookUp("class","tblclasses","Gender='" & [txtGender] & "' And " & [txtAge] & " between AgeLower And AgeUpper And " & [txtScore] & " between ScoreLower And ScoreUpper")

Not quite sure what your problem is with the other bit.

 

[killer23]

The calculations are on the form itself; how would I use a table etc . . .?

 

[Remou]

The way I imagined was a table with the fields I noted above and a textbox with a control source set to the =DlookUp line. The line references controls on the form which I have called txtGender, txtAge and txtScore, for the sake of illustration. The text box with DlookUp will update to a new Class when the referenced textboxes are changed.

 

[JoeAtWork]

One possibility is to create a public function that takes Gender, Age, and Score as parameters. Could be something like:

Public Function GetClass(Gender as String, Age as Integer, Score as Integer) as String

   If Gender="male" And Age>=27 and Score >=225 Then
      GetClass = "1st Class"
   ElseIf Gender="male" And Age<27 and Score >=230 Then
      GetClass = "2nd Class"
   End If
End Function

You could add as many ElseIf's as required to cover all possibilities. In your query you could replace the Iif function with the GetClass function.

 

[killer23]

Thanks JoeAtWork - I'll give it a shot and let you know what my results are.

 

[TheAceMan1]

How are ya killer23 . . .

It appears your encroaching on [blue]two lookup tables [/blue](male/female). Its certainly easy enough to lookup and the [blue]tables allow for expansion[/blue]. Could also be done in code with some finesse . . . depends on criteria for class selection.

Your thoughts? . . .

See Ya! . . . . . .

 

[killer23]

I tried to - incorrectly - modifiy the code above. I did not need the Gender portion (made a mistake in that assumption). I need the PFT_Score to upated the Class field accordingly:

Private Sub PFT_Score_AfterUpdate()

Dim Age As Integer
Dim PFT_Score As Integer

GetClass.Value = PFT_Score

If Age <= 26 And PFT_Score >= 225 Then
GetClass = "1st Class"
ElseIf Age <= 26 And PFT_Score >= 200 Then
GetClass = "2nd Class"
ElseIf Age <= 26 And PFT_Score >= 135 Then
GetClass = "3rd Class"
ElseIf Age <= 26 And PFT_Score <= 134 Then
GetClass = "Fail"
ElseIf Age <= 39 And PFT_Score >= 200 Then
GetClass = "1st Class"
ElseIf Age <= 39 And PFT_Score >= 150 Then
GetClass = "2nd Class"
ElseIf Age <= 39 And PFT_Score >= 110 Then
GetClass = "3rd Class"
ElseIf Age <= 39 And PFT_Score <= 109 Then
GetClass = "Fail"
ElseIf Age <= 45 And PFT_Score >= 175 Then
GetClass = "1st Class"
ElseIf Age <= 45 And PFT_Score >= 125 Then
GetClass = "2nd Class"
ElseIf Age <= 45 And PFT_Score >= 88 Then
GetClass = "3rd Class"
ElseIf Age <= 45 And PFT_Score <= 87 Then
GetClass = "Fail"
ElseIf Age <= 75 And PFT_Score >= 150 Then
GetClass = "1st Class"
ElseIf Age <= 75 And PFT_Score >= 100 Then
GetClass = "2nd Class"
ElseIf Age <= 75 And PFT_Score >= 65 Then
GetClass = "3rd Class"
ElseIf Age <= 75 And PFT_Score <= 64 Then
GetClass = "Fail"

End If
End Sub


Thanks for your help!

 

[killer23]

I've never done a table lookup - although I have a table for my second problem - that looks something like this:

Circ_Value 60 60_5 61 61_5 62 62_5
13
13.5 9 9
14 10 11 10 11
14.5 11 12 11 12 10 9
15 12 13 12 12 11 10
15.5 13 13 13 13 11 11

The idea being one would subtract "neck" from "waist" to equal "Circ_value" then drop down from the tope roq (equals = Height) to get the body fat %

Thanks for the input and any ideas that may help!

 

[MajP]

Hey Devil Dog this should work for the PFT Class.

Public Function PFTClass(intAge As Integer, intPFTScore As Integer) As String

   Select Case intAge
     Case 0 To 26
        Select Case intPFTScore
         Case 225 To 300
           PFTClass = "1st Class"
          Case 200 To 224
            PFTClass = "2nd Class"
          Case 135 To 199
            PFTClass = "3rd Class"
          Case Is < 134
            PFTClass = "Fail"
          End Select
      Case 27 To 39
        Select Case intPFTScore
           Case 200 To 300
             PFTClass = "1st Class"
          Case 150 To 199
            PFTClass = "2nd Class"
          Case 110 To 149
            PFTClass = "3rd Class"
          Case Is < 109
            PFTClass = "Fail"
        End Select
     Case 40 To 45
        Select Case intPFTScore
           Case 175 To 300
            PFTClass = "1st Class"
          Case 125 To 174
            PFTClass = "2nd Class"
          Case 88 To 124
            PFTClass = "3rd Class"
          Case Is < 87
            PFTClass = "Fail"
        End Select
     Case Else
   End Select
 
End Function

Public Sub textPFTClass()
  Debug.Print PFTClass(40, 187)
  Debug.Print PFTClass(45, 88)
  Debug.Print PFTClass(20, 160)
End Sub

Now you can use the function in any query or unbound control.

 

[MajP]

Killer23,

I would convert the table out of the order to this

tblBodyFat
snglCircValue (as single)
snglHeight (as single)
intBodyFat ( as integer)

so the first few records look like

13 60 9
13 60.5 9
14 60 11
14 60 11
14 61 10

Then you can build the following function

Public Function fncBodyFat(theNeck As Single, theWaist As Single, theHeight As Single) As Integer
  Dim snglCircValue As Single
  Dim strWhere As String
  snglCircValue = theWaist - theNeck
  strWhere = "snglCircValue = " & snglCircValue & " and snglHeight = " & theHeight
  fncBodyFat = DLookup("intBodyFat", "tblBodyFat", strWhere)
End Function

Pass in a neck, waist and get back a body fat

Hope this helps. Where are you stationed?

 

[killer23]

MajP -

I tried your solution - pasted the code into a module - saved as PFT class. When I bind this to the "Class" unbound text box the result is #Named. Any suggestions?

S/F,

Killer23 (GySgt)

 

[killer23]

MajP

Reserve Side
Phoenix - 6th Engineer Support - Bulk Fuel Co C - 1st Platoon Commander

Civ Side
State of Az - Tech Service / Web Manager

S/F
Killer23

 

[MajP]

You probably have fields Age and Score. In your unbound textbox you should have
=PFTClass([AGE],[SCORE])

 

[killer23]

MajP

What shows in the control source of my unbound text is:

=PFTClass([<<intAge>>],[<<intScore>>])

Both Age and Score are formatted as general numbers. Score is a field bound as such - =(Run_Score]+(P/U Score]+[Crunch_Score]) - if that has any bearing in the issues at hand.

S/F
Killer23

 

[MajP]

Since score is a calculated field try.
=PFTClass([intAge],([Run_Score]+[P/U Score]+[Crunch_Score]))
But,I guess this needs to be expanded for Women versus men.

I did not see that you had the formula for body fat. Are you using the table or the formula's or both. I have to run, but I will fix the function later.

 

[killer23]

MajP -

For body fat I would prefer to utilize the formula - it works perfectly in excel, but access seems to choke on Log10 functions; or, perhaps, it's my lack of skill within access is what's choking it.

Still no luck with your last solution however. By chance have already created such a database for your unit?

Thank again!
S/F
Killer23

P.S. - where are you located Sir?

 

[PHV]

access seems to choke on Log10 functions
Create your own Log10 function in a standard code module:

Public Function Log10(X)
    Log10 = Log(X) / Log(10#)
End Function

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

 

[Remou]

Why a lookup for body fat and a chunk of code for class? It seems much more difficult to maintain.

 

[killer23]

I'm code agnostic - meaning, I don't know how to code - thus I've come here to ask the experts. I concur that code would seem to be much more efficient for both issues rather than a lookup. The extent of my coding knowledge if very (VERY) basic IIf statements.

I will try the public log function and let you know how it turns out.

Again, thanks for all the help - I really do appreciate it.