Variable Calculated values

[patriciashoe]

Hi:

I have a need to have several different formulas for a calculated value on a form and report. Is there a way to store the formula in a field then refer to that field in the control source of the control property? Thanks,

Patti

[ADezii]

Hi:

I have a need to have several different formulas for a calculated value on a form and report. Is there a way to store the formula in a field then refer to that field in the control source of the control property? Thanks,

Patti

If I interpret you correctly, you must explicitly refer to the Formula/Function in the Control Source of the Control itself, and not to a Field containing the Formula. What, if I may ask, are you trying to accomplish?

[patriciashoe]

If I interpret you correctly, you must explicitly refer to the Formula/Function in the Control Source of the Control itself, and not to a Field containing the Formula. What, if I may ask, are you trying to accomplish?

I have a form with a calculated values that divides two fields. THis formula works for all but a few of the items in the form which requires a different calculation. In some cases the result that is currently obtained needs to further divided by another number.

[ADezii]

I have a form with a calculated values that divides two fields. THis formula works for all but a few of the items in the form which requires a different calculation. In some cases the result that is currently obtained needs to further divided by another number.

Why not?

1. Create a Calculated Field in the underlying Record Source for the Form. This Field would call a Public Function and pass to it the value of the Fields for each Record. Within the Function, the Fields would be analyzed, and the appropriate result would be passed back to the Calculated Field.
2. Apply similar logic in the Form's Current() Event.
3. If you need further clarification, just let me know.

[patriciashoe]

I can use a little more direction. Of the 150 records there are only six or seven that require a different formula. These records are flagged. Now, if you can elaborate just a little more on your solution, I can work through it. I can code but I have to work at it. thanks,

Patti

[ADezii]

I can use a little more direction. Of the 150 records there are only six or seven that require a different formula. These records are flagged. Now, if you can elaborate just a little more on your solution, I can work through it. I can code but I have to work at it. thanks,

Patti

Kndly provide me a little more information, so that we can get your on the right track:

1. Table Name.
2. Field Names that are involved in the Formulas and their Data Types.
3. Formula that is applied to 'Flagged' Records.
4. Formula that is applied to 'Non-Flagged' Records.
5. Flagged Field Name and Data Type.
6. Value in 'Flagged' Field that sets the Flag (True, "FormulaB", etc.).
7. Can either Field that is involved in the Formulas be NUll? (no value). If so, how do you handle this case?
8. Any other info you see as relevant.
9. I'll check back this afternoon, and we'll see what we can do.

[patriciashoe]

Thanks again,

The table is called teacherresoruce s. The fields are

buidlingID
TeacherFTE (number)
Enroll_students (number)
tr_year (number)
subjectID(numbe r) Note that in the subjects table there are unique subjectid, this is where I have a field called divisor to indicate a different formula. I was hoping to have the actual formula. Fro now it is a numeric field that I use to do the division as noted below.

The calculated value on the report and form is enroll_students/teacherFTE This works for 90 percent of the records. With the exception of two subjectid, the formula is changed as follows:

(student_enroll/teacherFTE)/5 or some other number. Right now I am doing the following. If there is no change in the formula the divisor is 1. Therefore the formula is (student_enroll/teacherfte)/[divisor]. I am plugging in the oher divisors and this seems to work. however, I anticipate greater complexity in the future so I don't know if this work around is viable in the long term. Two formulas are more involved and have to reference an enrollment field that is located on a sub form off the main form and is also part go query I am using. That is it in a nutshell. If you can get me moving in the right direction I can probably fill in the rest. Many thanks,

Patti

[NeoPa]

One way that may work is to define a Public Function in a standard code module that can handle a list of different formulas.
You would pass (from your SQL) parameters reflecting the formula required and the variables to be used.
For instance :

Public Function Compute(ByVal Formula As String, _
                        ByVal Students As Double, _
                        ByVal TeacherFTE As Double, _
                        ByVal Divisor As Double) As Double
  Select Case UCase(Formula)
  Case "MAIN"
    Compute = Students / TeacherFTE / Divisor
  Case "ALTERNATE1"
    Compute = 3.14159 * Students / TeacherFTE
  Case "ALTERNATE2"
    Compute = TeacherFTE / Students / Divisor
  End Select
End Function

[ADezii]

Thanks again,

The table is called teacherresoruce s. The fields are

buidlingID
TeacherFTE (number)
Enroll_students (number)
tr_year (number)
subjectID(numbe r) Note that in the subjects table there are unique subjectid, this is where I have a field called divisor to indicate a different formula. I was hoping to have the actual formula. Fro now it is a numeric field that I use to do the division as noted below.

The calculated value on the report and form is enroll_students/teacherFTE This works for 90 percent of the records. With the exception of two subjectid, the formula is changed as follows:

(student_enroll/teacherFTE)/5 or some other number. Right now I am doing the following. If there is no change in the formula the divisor is 1. Therefore the formula is (student_enroll/teacherfte)/[divisor]. I am plugging in the oher divisors and this seems to work. however, I anticipate greater complexity in the future so I don't know if this work around is viable in the long term. Two formulas are more involved and have to reference an enrollment field that is located on a sub form off the main form and is also part go query I am using. That is it in a nutshell. If you can get me moving in the right direction I can probably fill in the rest. Many thanks,

Patti

My old pal NeoPa has already pointed you in the right direction and has given the necessary logic.

[patriciashoe]

Thank you both!! I am on my way.

Patti