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]

i see the problem. It is a null hght. This is a update

Public Function getKCAL(ageInMonths As Variant, 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
  'If no age or weight then exit. Required values
  If IsNull(ageInMonths) Or IsNull(wghtInLBs) Then Exit Function
  wghtInKG = getKGfromLbs(CSng(wghtInLBs))
  If ageInMonths > 35 Then
    ageInYears = ageInMonths \ 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) 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

replace all others.

 

[Laguerrepj87]

This one works great.

Thank you for all your help

Majp

If i have anymore problem I will be looking for you

 

[MajP]

Sounds good. The function provides flexibility. So you can call it from lots of places. Instead of having a calculated control you could have a button and run code to populate the values in a control. Or you can use in in a query. So if you want to modify your design or use it somewhere else you have options.

 

[Laguerrepj87]

Morning I have a question.

With the ages 3 and up is there any ways to program it where it will allow a decimal value.

If a child is DOB is 01/01/07
the child is not 4 years old he is 4 years 4 months and 17 days old.
How do we get it do turn that into a decimal value like 4.4
to get a more accurate value.

I was thinking if I somehow make a DOB field then have it subtract it from the current date I could get that to work more effectively then the age ranges.

 

[MajP]

change this line of code
If ageInMonths > 35 Then
ageInYears = ageInMonths \ 12
End If

to
If ageInMonths >= 36 Then
ageInYears = ageInMonths / 12
End If

Now you would have to do your age combo differently and simply have values from 0-96 months.

or do away with the age combo all together if you are already tracking date of birth. You could simply pass in the date of birth instead of the ageInMonths. The function could calculate the age in months.

 

[MajP]

Do you want to have the user select months or simply calculate from DOB? Either is doable.

 

[MajP]

http://www.4shared.com/document/2ihTwp9S/CalcKCal3.html

This version has a new form. It uses the modified function. The form has a DOB text box. Another textbox calculates the age in decimal months. Then I pass that value to the function. Same function just different implementation.

 

[Laguerrepj87]

Hey im working on another style calculation and i used what you semd me for the first one.

IM getting and errr

Option Compare Database
Option Explicit

Public Function getKGfromLbs(lbs As Single)
getKGfromLbs = lbs * 0.4536
End Function

Public Function getCMetersFromInches(inches As Single)
getMetersFromInches = inches * 2.54
End Function
Public Function getAKCAL(ageyearss As Variant, wghtInLBss As Variant, hghtInCMeters As Variant, status As Variant) As Single
On Error GoTo errLbl
Dim wghtInKGs As Single
Dim hghtInCM As Single
Dim ageInYears As Single
Dim status As Integer
'If no age or weight then exit. Required values
If IsNull(ageyears) Or IsNull(wghtInLBss) Or IsNull(hghtInCMeters) Then Exit Function
wghtInKGs = getKGfromLbs(CSng(wghtInLBss))
hghtInCM = getCMetersFromInches(CSng(hghtInInches))

Select Case status
Case 0 To 1
getAKCAL = (9.99 * wghtInKGs + 6.25 * hghtInCM - 4.92 * ageInYears - 161) + 300

Case 2 To 3
getAKCAL = (9.99 * wghtInKGs + 6.25 * hghtInCM - 4.92 * ageInYears - 161)
Case 4 To 5
getAKCAL = (9.99 * wghtInKGs + 6.25 * hghtInCM - 4.92 * ageInYears - 161) + 500

End Select
Exit Function
errLbl:
MsgBox Err.Number & " " & Err.Description
End Function

 

[Laguerrepj87]

the link you posted shows that its been removed

 

[Laguerrepj87]

NVM i got it

 

[Laguerrepj87]

I just downloaded it.
and to your question yeah I just want them to input the date of birth and want the program to depermine the age.
so if the child is 4years 4 monthes and 17 days i want it to round it to 4years and 5 months and turn it to 4.4 then do the calculation

 

[Laguerrepj87]

Hey what does Compile Error
Duplicate Declarartion in current scope means

 

[MajP]

most of the time that error comes when you declare a variable twice. The most common is when you pass in a parameter to a procedure and then declare it again.


public sub someProcedure(someVariable as variant, someOtherVariable as integer)
'I am passing in something called someVariable as a parameter above
'I cannot not then dimension a variable with that name.
'This is a duplicate declaration
dim someVariable as variant
end sub

The other thing I cannot do is have a procedure or module with the same name

I cannot have a module with a public procedure named "procedureOne"
and another module also with a public procedure called "procedureOne"
The compiler will not allow this because it would not know which one you ar trying to use.

 

[Laguerrepj87]

the problem is
i cant find any dups

from the other coding
i addes exstra letters to them.

 

[MajP]

please post the code.

 

[Laguerrepj87]

Public Function getAKCAL(ageinYears As Variant, wghtInLBs As Variant, hghtInCmeters As Variant, status As Variant) As Single

On Error GoTo errLbl
Dim wghtInKG As Single
Dim hghtInCM As Single
Dim ageinYears As Single
Dim status As Variant
'If no age or weight then exit. Required values
If IsNull(ageinYears) Or IsNull(wghtInLBss) Or IsNull(hghtInCmeters) Then Exit Function
wghtInKG = getKGfromLbs(CSng(wghtInLBss))
hghtInCM = getCMetersFromInches(CSng(hghtInInches))

Select Case status
Case 0 To 1
getAKCAL = (9.99 * wghtInKGs + 6.25 * hghtInCM - 4.92 * ageinYears - 161) + 300

Case 2 To 3
getAKCAL = (9.99 * wghtInKGs + 6.25 * hghtInCM - 4.92 * ageinYears - 161)
Case 4 To 5
getAKCAL = (9.99 * wghtInKGs + 6.25 * hghtInCM - 4.92 * ageinYears - 161) + 500

End Select
Exit Function
errLbl:
MsgBox Err.Number & " " & Err.Description
End Function
its highlighting
the top in yellow

Public Function getAKCAL(ageinYears As Variant, wghtInLBs As Variant, hghtInCmeters As Variant, status As Variant) As Single

 

[MajP]

I see lots of problems. Not sure how you are not seeing it. You are passing variables in and then dimensioning them. It is exactly as I described previously.

Public Function getAKCAL([b]ageinYears[/b] As Variant, wghtInLBs As Variant, hghtInCmeters As Variant, [b]status[/b] As Variant) As Single

  On Error GoTo errLbl
  Dim wghtInKG As Single
  Dim hghtInCM As Single
  'duplicate
  Dim [b]ageinYears[/b] As Single
  'duplicate
  Dim [b]status[/b] As Variant
  'If no age or weight then exit. Required values
  If IsNull(ageinYears) Or IsNull(wghtInLBss) Or IsNull(hghtInCmeters) Then Exit Function
  'Spelling
  wghtInKG = getKGfromLbs(CSng([b]wghtInLBss[/b]))
  'Does not exist
  hghtInCM = getCMetersFromInches(CSng([b]hghtInInches[/b]))

 

[Laguerrepj87]

Morning,
I see what you're saying and I have removed the Sups

heres the code and its not giving me an error anymore
but its not computing the right answer

please take a look



Option Compare Database
Option Explicit

Public Function getKGfromLbs(lbs As Single)
getKGfromLbs = lbs * 0.45359237
End Function

Public Function getMetersFromInches(inches As Single)
getMetersFromInches = inches * 0.0254
End Function
Public Function GetCmFromInches(inches As Single)
GetCmFromInches = inches * 2.54
End Function


Public Function getAKCAL(wghtInLBs As Variant, hghtInInchess As Variant, ageinYears As Variant, status As Variant)
On Error GoTo errLbl
Dim wghtInKG As Single
Dim hghtInCM As Single
Dim age As Single
'If no age or weight then exit. Required values
If IsNull(ageinYears) Or IsNull(wghtInLBs) Or IsNull(hghtInInchess) Then Exit Function
wghtInKG = getKGfromLbs(CSng(wghtInLBs))
hghtInCM = GetCmFromInches(CSng(hghtInInchess))
Select Case status
Case 0 To 1
getAKCAL = 9.99 * wghtInKG + 6.25 * hghtInCM - 4.92 * age - 161 + 300
Case 2 To 3
getAKCAL = 9.99 * wghtInKG + 6.25 * hghtInCM - 4.92 * age - 161
Case 4 To 5
getAKCAL = 9.99 * wghtInKG + 6.25 * hghtInCM - 4.92 * age - 161 + 500
End Select
Exit Function
errLbl:
MsgBox Err.Number & " " & Err.Description
End Function

 

[PHV]

Dim age As Single
age is ALWAYS equal to ZERO as it isn't calculated from ageinYears ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Laguerrepj87]

Public Function getKCAL(ageinmonths As Variant, 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
  'If no age or weight then exit. Required values
  If IsNull(ageinmonths) Or IsNull(wghtInLBs) Then Exit Function
  wghtInKG = getKGfromLbs(CSng(wghtInLBs))
  If ageinmonths > 35 Then
    ageinYears = ageinmonths \ 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) 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

Question: Is there a way to take the date of birth from cases 36 and up, the actual date of birth like 03/1989 and have the computer calculate the persons exact age for example if a person was 22 years old and 2 months would it be possible for the computer to compute that the person is not 22 years old but 22 years and 2 months. Then turn that number into a decimal so it can input it in to case functions for [AgeInyears]. Now we dont want the decimal answer to be 22.2 as in 22 years old and 2 months becuase there are 12 months in a year not 10 so we would like it to turn the months into the correct decimal EX) todays date year and month 2011/05 subtracted by the birthdate year and month 1989/03 would be 22years and 2months and (2/12=.166666) so infact the person is 22.166666 years old.

How would we write that in code?