Making calculations from a form. 1

[Laguerrepj87]

Hey I need help.

I need to know how to calculate different calculations performed depending on the selection.
If someone select age from a combo box it will use a formula designed for that age.
Then the input from the form will automatically input what is required into the formula.

This is what I mean.

from ages 0-3mo (89*[what weight they input from the form](convert it to Kg)- 100 +175 Kcal

from ages 4-6mo (89*[what weight they input from the form](convert it to Kg)- 100 +56 Kcal

from ages 7-12mo (89*[what weight they input from the form](convert it to Kg)- 100 +22 Kcal

from ages 13-36mo (89*[what weight they input from the form](convert it to Kg)- 100 +175 Kcal

this is where it gets kind of tricky

from ages Boys 3-8 years old 88.5-(61.9*[age picked from the form(combo box)}+[PA input from the form]*(26.7*[weight input from the form( and convert it to [kg]]+903*[height the input from form(list box)&(convert it to [m]])+20


from ages Girls 3-8 years old 135.3-(30.8*[age picked from the form(combo box)+[PA input from the form]*(10.0*[weight input from the form( and convert it to [kg]]+934*[height the input from form(list box)&(convert it to [m]])+20

I don’t know if I should design a query that does all these calculations by breaking the calculations into a table then having the query criteria do all the work.

I’m not a programmer but unfortunately I have to get it done.

Any ideas?

 

[MajP]

See my post of 18 May 10:05. As I said the demo does exactly that.

 

[Laguerrepj87]

wouldnt the only way to have that would be to have a birthdate field?? If a child was born 2 years and 3 months ago how would the computer differentiate from a achild born 2 years and 5 months ago??

 

[MajP]

Maybe I do not understand, but I do not see the issue. The way I set it up was this.

You enter a date on a form, and this function returns the age in decimal months (Biological not calendar).

Public Function decimalMonths(dtmDate As Variant) As Single
  If IsDate(dtmDate) Then
    decimalMonths = DateDiff("d", dtmDate, Now) / 30.44
  End If
End Function

to get decimal years I divide decimal months by 12

using 3/1/1989 I get
266.5572 decimal months
22.2131 decimal years

Originally I had the wrong avg days in a month. Should be 30.44

 

[MajP]

So if you were not following. Originally I had a field on a form to enter DOB. I used the decimalMonths function to calculate the months as a decimal, and that is what I passed to the function.

The following is no different except where and when I do that decimal months calculation. I can just pass in a DOB, and then have the original function calculate the decimal months vice doing it prior.

Public Function getKCAL([b]DOB as variant[/b], wghtInLBs As Variant, Optional Sex As Variant = "B", Optional PA As Variant = 0, Optional hghtInInches As Variant = 0) As Single
  On Error GoTo errLbl
  Dim wghtInKG As Single
  Dim hghtInM As Single
  Dim ageInYears As Integer
  'Since not a parameter need to add a variable
  [b]dim ageInMonths as single [/b]

  'If no age or weight then exit. Required values
  If not Isdate(DOB) Or IsNull(wghtInLBs) Then Exit Function
  wghtInKG = getKGfromLbs(CSng(wghtInLBs))
  'get the decimal months
  [b] ageInMonths = decimalMonths(DOB) [/b]
  'get the decimal years
  ageInYears = ageinmonths \ 12
  
  Select Case ageinmonths
     Case 0 To 3
        getKCAL = (89 * wghtInKG - 100) + 175
     Case 4 To 6
        getKCAL = (89 * wghtInKG - 100) + 56
     Case 7 To 12
        getKCAL = (89 * wghtInKG - 100) + 22
     Case 13 To 35
        getKCAL = (89 * wghtInKG - 100) + 20
     Case 36 To 96
         'If no hght or PA then exit need these values
         If IsNull(hghtInInches) Or IsNull(PA) Then Exit Function
         hghtInM = getMetersFromInches(CSng(hghtInInches))
        If Sex = "B" Then
           getKCAL = 88.5 - (61.9 * ageinYears) + PA * (26.7 * wghtInKG + 903 * hghtInM) + 20
        ElseIf Sex = "G" Then
          getKCAL = 135.3 - (30.8 * ageinYears) + PA * (10# * wghtInKG + 934 * hghtInM) + 20
        End If
     End Select
     Exit Function
errLbl:
     MsgBox Err.Number & " " & Err.Description
End Function

 

[Laguerrepj87]

hey i did follow you. and you have Kcal come up automaticly and i didnt see the other form.
but i ended up addeing two fields and year and month
and just added some code take a look.

Public Function getKCAL(ageInMonths As Variant, wghtInLBs As Variant, Optional Sex As Variant = "B", Optional PA As Variant = 0, Optional hghtInInches As Variant = 0, Optional year As Variant = 0, Optional months As Variant = 0) As Single
  On Error GoTo errLbl
  Dim wghtInKG As Single
  Dim hghtInM As Single
  Dim yearOld As Single
  'If no age or weight then exit. Required values
  If IsNull(ageInMonths) Or IsNull(wghtInLBs) Then Exit Function
  wghtInKG = getKGfromLbs(CSng(wghtInLBs))
  If ageInMonths >= 36 Then
  yearOld = ((year * 12) + months) / 12
  End If
  Select Case ageInMonths
     Case 0 To 3
        getKCAL = (89 * wghtInKG - 100) + 175
     Case 4 To 6
        getKCAL = (89 * wghtInKG - 100) + 56
     Case 7 To 12
        getKCAL = (89 * wghtInKG - 100) + 22
     Case 13 To 35
        getKCAL = (89 * wghtInKG - 100) + 20
     Case 36 To 96
         'If no hght or PA then exit need these values
         If IsNull(hghtInInches) Or IsNull(PA) Or IsNull(year) Then Exit Function
         hghtInM = getMetersFromInches(CSng(hghtInInches))
         
        If Sex = "B" Then
           getKCAL = 88.5 - (61.9 * yearOld) + PA * (26.7 * wghtInKG + 903 * hghtInM) + 20
        ElseIf Sex = "G" Then
          getKCAL = 135.3 - (30.8 * yearOld) + PA * (10# * wghtInKG + 934 * hghtInM) + 20
        End If
     End Select
 
     Exit Function
errLbl:
     MsgBox Err.Number & " " & Err.Description
End Function

Now i need help making reports