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]

It is hard to answer without knowing how your form and fields are set up. I would build some functions to do the work. A function can then be called from in a query or calculated control and you can pass the values from the controls.

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

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

Public Function getKCAL(ageInMonths As Integer, wghtInLBs As Single, Optional Sex As String, Optional PA As Single, Optional hghtInInches As Single) As Single
  'no idea of what units you input in I will assume months, lbs, and inches
  Dim wghtInKG As Single
  Dim hghtInM As Single
  wghtInKG = getKGfromLbs(wghtInLBs)
  hgthInM = getMetersFromInches(hghtInInches)
  Select Case ageInMonths
     Case 0 To 3
   'put parenthese in the correct place it was unclear
        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 36
        getKCAL = 89 * (wghtInKG - 100) + 175
        'seems like a mistake with 175 repeated
     Case 37 To 48
        If Sex = "M" Then
          getKCAL = 88.5 - (61.9 * ageInMonths + PA) * 26.7 * wghtInKG + (903 * hghtInM) + 20
        ElseIf Sex = "F" Then
          '....
        End If
     End Select
End Function

to use this in a calculated control or query you pass the field or control name into it. So on a form in a calculated control

=getKCal(cmboAge, txtboxWght, txtBoxAge,cmboSex, txboxPA, txtboxHght)

or in an event from a form you could call the function by passing in the values.

dim kcals as single
kcals = getKcals(me.fieldname, me.fieldname,me.controlname...)

 

[Laguerrepj87]

Ok im still lost, but thats because im not so great at access.
I pasted the code in the VB code.

I dont know how to tie the code to the form.
and another thing I tried to use the same format you had for the coding of the formula please check to see if i did it right.






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

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

Public Function getKCAL(ageinyear As Integer, ageInMonths As Integer, wghtInLBs As Single, Optional Sex As String, Optional PA As Single, Optional hghtInInches As Single) As Single
'no idea of what units you input in I will assume months, lbs, and inches
Dim wghtInKG As Single
Dim hghtInM As Single
wghtInKG = getKGfromLbs(wghtInLBs)
hgthInM = getMetersFromInches(hghtInInches)
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 36
getKCAL = 89 * (wghtInKG - 100) + 20
Case 37 To 48
If Sex = "B" Then
getKCAL = 88.5 - (61.9 * ageinyear) + PA * (26.7 * wghtInKG + 903 * hghtInM) + 20
ElseIf Sex = "G" Then
getKCAL = 135.3 - (30.8 * ageinyear) + PA * (10# * wghtInKG + 934 * hghtInM) + 20
'....
End If
End Select
End Function

 

[MajP]

How to tie a function to a form

Imagine a simple function that takes values from 2 fields and adds them together and then adds 100. You build a function with 2 parameters to pass in the values.

public getValue (dblVal1 as double, dblVal2 as double) as double
getValue = dblVal1 + dblVal2 + 100
end function

to call this from a form you can simply add an unbound control and set its control source and pass in the controls that hold the values or the field.

assume the fields are called field1 and field2 and they are bound to txtbox1 and txtbox2.

on the form in the control source of on unbound control
=getValue([field1],[field2])
or using controls
= getValue([txtBox1],[txtBox2])

Or you can build code on the form that reads the values and passes to the function.

To help verify this function could you pass a few sets of input values and what answer you would expect?

 

[Laguerrepj87]

It didnt do anything.
I made a unbound list on the same form the weight height ect
are being made from.
the i put this

=getKCal([ageinput],[wtinput],[sex],[PA],[htinput])

in to the contol source
0
I got it form this what you sent me but i just changed it to the right names that are in my form.

=getKCal(cmboAge, txtboxWght, txtBoxAge,cmboSex, txboxPA, txtboxHght)

I know i must be doing something wrong but I dont know what

Any ideas?

Thanks You!

 

[MajP]

in to the contol source
0
I got it form this what you sent me but i just changed it to the right names that are in my form.

Does that say you get 0 in your control? If that is the case that is actually good news. That means your call to the function is working, if not you would have got an error so half the battle is done. It may then be a problem in the function.

[ageinput],[wtinput],[sex],[PA],[htinput]

Are they the names of your controls? or are they names of bounds fields? If so what are the values of each of those controls/fields and what would you expect. If you provide input values I can check the logic.
What units are age, wt, and ht?

 

[Laguerrepj87]

Hey,

Im sorry it didnt say 0 ( nothing shows)

those are the name of the feild that in my form.

I attached a PNG file to this so you can see how my form is setup.

 

[Laguerrepj87]

sorry i marked the wrong spot in the picture.
height should be the field in front of weight.

as people input the weight the height the PA and select the age range I want the calculation form the formulas to show in
Kcal(list box)
the age is there so that it can determine the formula to use
the only time the actual age is important is if a child is 3 years old and up.

so on my form I made a List boxes for weight, height, PA,
now I made a combo box where they would have to select the age range ( I thought that would be the best in trying to get the right formula to be used.

 

[MajP]

with a combo box you can hide a column, but use that column for the value. If you want to do that build a simple table.

ageValue ageDescription.
0 O to 3 Months
4 4 to 6 Months
7 7 to 12 Months
..
37 > 3 yrs to 4 yrs


set the combo box recordsource to this table.
Set the column count to 2
set the column withs to 0;"1"
Set the bound column to 1

This will display a combo like
O to 3 Months
4 to 6 Months
7 to 12 Months

But if I click on the second choice the combo will have a value of 4

The function is set to work off months values so it will not work with a text value of "0 to 3 months"

Again it would be real helpful for you to give me input values and expected outputs so I can test the function and validate it is correct. Also need to know the units you are using for height and weight.

 

[Laguerrepj87]

WHen the applicant chooses their age range from the Age range field, I want it to automically determine which formula to use, after determining which formula to use I want it to automatically take the hieght which is inputed by the applicant in (inches) and the wight which is also inputted by the applicant in (lbs) and calculate the kcal by the formula chosen.

The only thing is that ages ranging from 0-36 months only need the weight to actually compute the calculation.

Ages 3-8 in Boy's need age,weight,hieght and the PA fields to compute the calculation.

Now ages 3-8 in Girls need all the fields also to calculate the formula but the actual formula is different from the one used for Boys.

The ages are in a drop down combo box(which is created in a table) to be chosen by the applicant and the weight and hight and PA fields are to be manually inputted by the applicant.

Example)

A boy that is 4 years old, he has a PA of 1.13, his weight is 45 lbs, his hieght is 3ft 4 inch. The formula that would be used to calculate his Kcal would be [88.5-(61.9*age)+PA*(26.7*weight[kg]+903*Hieght[m]+20)]

 

[MajP]

I need to know what you think the answer is for the above. I think you need more parentheses because as written the mathematical order of operation may not be what you think. It does not look correct as written. Computers like mathematics is OOO: Parentheses, then multiplication, division, addition, subtraction. So
[88.5-(61.9*48)+1.13*(26.7 * 20.41 + 903 * 1.02 + 20)]
88.5 - (2971.2) +1.13 * (544.95 + 921.06 + 20)
88.5 - (2971.2) + 1.13 * (1486.01)
88.5 - 2971.2 + 1679.1913
88.5 - 4650.3913
-4561.8913 (Doubt it)

 

[MajP]

http://en.wikipedia.org/wiki/Order_of_operations

You see that you have to be pretty precise because not even all calculators and computers use the same order of operations.

 

[Laguerrepj87]

Sorry here’s the formula from the book.

88.5-(61.9*Age[y])+PA*(26.7*weight[kg]+903*height[m])+20Kcal
thats the formula to use for boys 3-8 years old.

 

[Laguerrepj87]

ages should be calculated in years not months
I think that’s why we had that big number.
I just looked at your steps and the 48 should be 4
it wants it in years not months to compute the calculations

 

[MajP]

That helps let me take a look.

 

[Laguerrepj87]

o yeah the height should all be in inches not ft

 

[MajP]

Here is an updated function.

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
  'no idea of what units you input in I will assume months, lbs, and inches
  Dim wghtInKG As Single
  Dim hghtInM As Single
  Dim ageInYears As Integer
  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 48
         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
End Function

Here is my age combo. The first column is bound and hidden.
0 0-3 mo
4 4-6 mo
7 7-12 mo
13 13-35 mo
36 3 yrs
48 4 yrs
60 5 yrs
72 6 yrs
84 7 yrs
96 8 yrs

Here is my function call
=getkcal([cmboAge],[txtbxwght],[cmbosex],[txtbxpa],[txtbxhght])

Since it is a function, it can be used in a lot of different ways not just an unbound control.

 

[MajP]

here is a working demo.

http://www.4shared.com/document/kS8lc013/CalcKCal.html

 

[Laguerrepj87]

hey I got the demo and it works. I am getting a debug for the height


hghtInM = getMetersFromInches(CSng(hghtInInches))

thats in yellow

but it still works
any idea why its doing this

 

[MajP]

What is the error statement?
Also change the last case statement from
Case 36 To 48
to
Case 36 to 96