Formulas in Table for use in Visual statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
Krizhek said:
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.
Click to expand...


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,
Click to expand...
"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.
 
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.

Marshall Barton said:
Krizhek said:
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.
Click to expand...


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,
Click to expand...
"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.
Click to expand...
 
Back
Top