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

[NeoPa]

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

I suspect I just managed to squeeze in there quickly before you provided something very similar ADezii ;)

@Patti Let us know how you get on with it.

[patriciashoe]

Gents:

I have coded my formulas and have test them. Can you suggest way that I can code the following:

In my grade table I have a field that would indicate which formula is to be used. On my form and my reports I would like to use this field to programatically set up the function. For example, for gradeid 7 the formula would be "MAIN". When the report runs I need to have the calculated value reflect that I am using the "MAIN" formula. I have about 144 gradeid records.Current ly this is the code I have in the report for this calculated value: THis is using my old method.

=IIf([student_enroll]=0,0,([student_enroll]/[teacherfte])/!staffingDivisor)

Thanks for all the help. This project is just about completed.

Patti

[NeoPa]

You've said you want to use a particular field to determine which algorithm to use, but you haven't left any clues as to the logic behind it, or even what values are likely to be found in that field (I'm assuming the field is called [GradeID]).

It could be that you could store the algorithm name in [GradeID]. If so it's quite easy. If not, then we need some indication of what you want it to do. If it's likely to get at all complicated in the future, I'd advise setting up the mapping in a table rather than in code.

Let us know.

[patriciashoe]

Sorry if I am not being very clear. I guess I am looking for the easiest way to use these formulas in a text box based on a record in the table. If I have a numeric field called formulaid for each gradeid, then how do I indicate what formula should be in the text box calculated value?

table data example

gradeid numberic
gradename text
buildingid
formulaid

For example:this is what I am thinking could work

if the formulaid = 1 then call the function using the first formula in the case statement.

if there is a better way to day this please let me know. Thank again,

Patti

[NeoPa]

This is very confusing. Not much of the information you give relates to the other information, so all we have is independent snippets.

To user the Compute() function, we need to have values for each of the four parameters (Formula; Students; TeacherFTE; Divisor).

Let us see if we can't guess some of what's going on and you can tell us if we are on the right lines.

You have a TeacherResource s table something like :

Table = [[U]TeacherResources[/U]]
[I]FieldName; Type; Index[/I]
BuidlingID; Numeric
TeacherFTE; Numeric
Enroll_Students; Numeric
Tr_Year; Numeric
SubjectID; Numeric; FK

You have a Subjects table something like :

Table = [[U]Subjects[/U]]
[I]FieldName; Type; Index[/I]
SubjectID; Numeric; PK
Divisor; Numeric

So far, we have the Students, TeacherFTE and Divisor parameters (as the SubjectID in TeacherResource s matches the SubjectID in Subjects).
Now, we have a Grades table, but no discernible way to access it :

Table = [[U]Grades[/U]]
[I]FieldName; Type; Index[/I]
GradeID; Numeric; PK
GradeName; String
BuildingID; Numeric
FormulaID; Numeric

When dealing with a TeacherResource item, how can we find the right Grade that goes with it?