Formulas in Table for use in Visual statement

[Guest]

I am attempting to create a way, in access 03, to store simple if statements
in a table which are rules for various results. I want to be able to pull the
text of the statement to use in a visual basic statement to produce the final
value in a report.

The current table has the below fields
GoalName : GradeReq : Grade Rule

the GoalName (Name of the Goal for the person) and GradeReq (the postion the
goal applies for) are used with a dlookup statement in the visual basic code
to pull the correct goalrule.

Here is an example of the Goal rule:

IIf(cersRST <= 1 And cersRST > 0.9, 5, IIf(cersRST > 0.899 And cersRST <
0.85, 4, IIf(cersRST > 0.849 And cersRST < 0.8, 3, IIf(cersRST > 0.799 And
cersRST < 0.75, 2, IIf(cersRST < 0.749, 1, "Error")))))

Here is a snippit of the code I am using to get the results


Private Sub Report_Activate()
Dim cersRST As Integer
Dim cersGoalRule As String
On Error GoTo Err_Report_Activate
cersRST = Me.Report1subreport.Report.[Result3_Value]
'cersRST is the result from the associate used in the
'iff statement determine the final result
cersGoalRule = DLookup("[graderule]", "GradeRules", "[GoalName]= " &
"'" & Me.[GoalName] & "'" & " And [GradeReq] = " & "'" & Me.[GradeReq1] & "'")
Me.Rule = cersGoalRule
Exit_Report_Activate:
Exit Sub

Err_Report_Activate:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Report_Activate
End Sub

What I am hopeing to avoid is to be able to change goals for our associates
in the table and not have to reprogram the report.

 

[Marshall Barton]

I am attempting to create a way, in access 03, to store simple if statements
in a table which are rules for various results. I want to be able to pull the
text of the statement to use in a visual basic statement to produce the final
value in a report.

The current table has the below fields
GoalName : GradeReq : Grade Rule

the GoalName (Name of the Goal for the person) and GradeReq (the postion the
goal applies for) are used with a dlookup statement in the visual basic code
to pull the correct goalrule.

Here is an example of the Goal rule:

IIf(cersRST <= 1 And cersRST > 0.9, 5, IIf(cersRST > 0.899 And cersRST <
0.85, 4, IIf(cersRST > 0.849 And cersRST < 0.8, 3, IIf(cersRST > 0.799 And
cersRST < 0.75, 2, IIf(cersRST < 0.749, 1, "Error")))))

Here is a snippit of the code I am using to get the results


Private Sub Report_Activate()
Dim cersRST As Integer
Dim cersGoalRule As String
On Error GoTo Err_Report_Activate
cersRST = Me.Report1subreport.Report.[Result3_Value]
'cersRST is the result from the associate used in the
'iff statement determine the final result
cersGoalRule = DLookup("[graderule]", "GradeRules", "[GoalName]= " &
"'" & Me.[GoalName] & "'" & " And [GradeReq] = " & "'" & Me.[GradeReq1] & "'")
Me.Rule = cersGoalRule
Exit_Report_Activate:
Exit Sub

Err_Report_Activate:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Report_Activate
End Sub

What I am hopeing to avoid is to be able to change goals for our associates
in the table and not have to reprogram the report.

Well, your IIf is more than a little garbled. You could get
a result from something more like:

IIf(cersRST >= 0.9, 5, IIf(cersRST >= 0.85, 4, IIf(cersRST

= 0.8, 3, IIf(cersRST >= 0.75, 2, IIf(cersRST >= 0.7, 1,

"Error")))))

But I think this would be easier to understand:

Switch(cersRST >= 0.9, 5, cersRST >= 0.85, 4, cersRST >=
0.8, 3, cersRST >= 0.75, 2, cersRST >= 0.7, 1, True,
"Error")

To use those expressions the way you want, your code would
be like:

Me.Rule = Eval(Replace(cersGoalRule, "cersRST", cersRST))

The reason for the Replace is because VBA variables are not
included in the Eval function's namespace.

 

[Guest]

Thanks Marshall spent half of the morning on this one. Sorta disappointed
that all I needed was the Eval function

Also thanks on the tip on the iif statement.

I am attempting to create a way, in access 03, to store simple if statements
in a table which are rules for various results. I want to be able to pull the
text of the statement to use in a visual basic statement to produce the final
value in a report.

The current table has the below fields
GoalName : GradeReq : Grade Rule

the GoalName (Name of the Goal for the person) and GradeReq (the postion the
goal applies for) are used with a dlookup statement in the visual basic code
to pull the correct goalrule.

Here is an example of the Goal rule:

IIf(cersRST <= 1 And cersRST > 0.9, 5, IIf(cersRST > 0.899 And cersRST <
0.85, 4, IIf(cersRST > 0.849 And cersRST < 0.8, 3, IIf(cersRST > 0.799 And
cersRST < 0.75, 2, IIf(cersRST < 0.749, 1, "Error")))))

Here is a snippit of the code I am using to get the results


Private Sub Report_Activate()
Dim cersRST As Integer
Dim cersGoalRule As String
On Error GoTo Err_Report_Activate
cersRST = Me.Report1subreport.Report.[Result3_Value]
'cersRST is the result from the associate used in the
'iff statement determine the final result
cersGoalRule = DLookup("[graderule]", "GradeRules", "[GoalName]= " &
"'" & Me.[GoalName] & "'" & " And [GradeReq] = " & "'" & Me.[GradeReq1] & "'")
Me.Rule = cersGoalRule
Exit_Report_Activate:
Exit Sub

Err_Report_Activate:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Report_Activate
End Sub

What I am hopeing to avoid is to be able to change goals for our associates
in the table and not have to reprogram the report.

Well, your IIf is more than a little garbled. You could get
a result from something more like:

IIf(cersRST >= 0.9, 5, IIf(cersRST >= 0.85, 4, IIf(cersRST

= 0.8, 3, IIf(cersRST >= 0.75, 2, IIf(cersRST >= 0.7, 1,

"Error")))))

But I think this would be easier to understand:

Switch(cersRST >= 0.9, 5, cersRST >= 0.85, 4, cersRST >=
0.8, 3, cersRST >= 0.75, 2, cersRST >= 0.7, 1, True,
"Error")

To use those expressions the way you want, your code would
be like:

Me.Rule = Eval(Replace(cersGoalRule, "cersRST", cersRST))

The reason for the Replace is because VBA variables are not
included in the Eval function's namespace.