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 teacherresoruces. The fields are

buidlingID
TeacherFTE (number)
Enroll_students (number)
tr_year (number)
subjectID(number) 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 teacherresoruces. The fields are

buidlingID
TeacherFTE (number)
Enroll_students (number)
tr_year (number)
subjectID(number) 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.Currently 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 TeacherResources table something like :

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

You have a Subjects table something like :

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

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

Table = [Grades]
 FieldName; Type; Index
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?

[NeoPa]

When we have that information, we can look at how we should access the Formula code that the Select statement works from. I would think that it can be stored in the Grades table. Either in place of, or as well as, FormulaID. I can't see any reason for having FormulaID unless it acts as a Foreign Key (FK) into another table for handling formula codes. This may be a good way to do it if there are a large number of them.

Another alternative is to change the code in Compute() to work from the FormulaID numbers instead of the codes. The only reason I suggested strings in the first place is that it's easier to work with and remember if you can assign a string with a meaning, rather than a simple number with no connection to the formula itself.

[patriciashoe]

Sorry for the confusion. Here are the data structures I am using:

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

Table = [Grades]
 FieldName; Type; Index
SubjectID; Numeric; PK
GradeName; String
BuildingID; Numeric
FormulaID; Numeric
Divisor; nummeric

The tables are linked as noted. Your suggestion to use a numeric for the case statement could do the trick. How about something like this

=IIf([student_enroll]=0,0,myfunction([formulaID],[enroll_students],teacherfte]

Thanks again, Patti

[NeoPa]

The line would look something like :

=Compute([FormulaID], [Enroll_Students], [TeacherFTE], [Divisor])

You can call it MyFunction if you prefer of course.
There is no need for an IIf() call. Ensure in the code that division by zero is always avoided.

[NeoPa]

I should advise you by the way Patti, that naming can be very important. If you have a PK called [SubjectID] in a [Grades] table then this is likely to confuse people. Not just in a forum like this, but anyone having to deal with the database in a maintenance capacity - including yourself later on.

In a question, it makes it very important for you to make this clear before you start otherwise confusion is almost certain to ensue.

In short, proper naming can be considered unimportant, but never by those with experience.

[patriciashoe]

Thank you!!! Everything is working just fine. I did take note of your advice about proper naming.

Thanks again, Patti

[NeoPa]

I'm really pleased to hear that it's working for you Patti :)

That problem was not a simple one, and is a good illustration of where a bit of lateral thinking can provide real flexibility.