EVAL() function and formula templates

[cpmasesa]

Hi,

I am trying to use formula templates in my application

I have table with the formula templates for different scenarios. TableName = CorrRules. Each scenario has a scenarioID in this table. One of my formuala templates is
[ProbVar]&" == " & [ProbVal]. This is stored in the field Formula2Use

I have another table, TableName = Checks, which I need to use to create my desired output based on the formula templates. Each record in this table also has a scenarioID

I link the tables on ScenarioID and create the field
myformula: eval_mmm([formula2Use])

Eval_mmm() is a wraper function:
Public Function eval_mmm2(str2evaluate As string)
eval_mmm = Eval(QuotedStr(str2Evaluate))
End Function

QuotedStr is another wrapper function defined as follows:
Public Function QuotedStr(String2Quote) As String
QuotedStr = Chr(34) & String2Quote & Chr(34)
End Function


When I run this code I get MyFormula returning 0 (zero)

However when I write myformula as:
eval_mmm([ProbVar]&" == " & [ProbVal])

replace Formula2Use, the field holding my formula template, with the actual formula template it works!

Why does it fail in the first instance?

TIA

Clemens

 

[MajP]

Aren't you unnecessarily wrapping a string?

Public Function QuotedStr(String2Quote) As String
QuotedStr = Chr(34) & String2Quote & Chr(34)
End Function

If it is a string already does so instead of evaluating
eval_mmm([ProbVar]&" == " & [ProbVal])
you are evaluating
eval_mmm("[ProbVar]&" == " & [ProbVal]")

ex

dim x as string
x = "some string"
debug.print x
'would be: some string
debug.print Chr(34) & x & chr(34)
'would be: "some string

 

[cpmasesa]

I agree.

When I drop the string wrapper I get the error message:
Runtime error 2482

Microsoft access can not find the name 'probvar' you entered in the expression.

I had put the string wrapper while testing passing the template manually rather than through a field

Clemens

 

[MajP]

I am not sure if that is a typo, but maybe
probvar should be ProbVar?

 

[cpmasesa]

The case does not make a difference.

I tried, . I get the same results if I type probvar or ProbVar

I just tried DLookup(), after looking around on the web for possible solutions, but then it evaluates only once and so all the rows have the same calculated value, which should not be the case.

Clemens

 

[MajP]

If this works:
eval_mmm([ProbVar]&" == " & [ProbVal])

1) do a debug.print [ProbVar]&" == " & [ProbVal]
and then do a debug.print to show the string that you are actually evaluating. There has to be a difference.

my guess is the first one returns something like
q0123 == 02
and the second still returns
[ProbVar] == [ProbVal]

 

[cpmasesa]

debug.print ProbVar & " == " & ProbVal results in == ! (had to remove [], vba complained that external references not found. removed also in my formula template file

debug.print "ProbVar & ' == ' & ProbVal" results in:
ProbVar & ' == ' & ProbVal (exactly the same thing without the "

debug.print eval("ProbVar & ' == ' & ProbVal") results in runtime error 2482 as described previously, vba can not find ProbVar!!!!


For the manual encoding of the formula template to work I MUST enclose the template in " using either:
Eval(quotedstr([probvar] & " = " & [correction]))
OR
include the call to QuotedStr in the wrapper function eval_mmm()

Do soing works perfectly

Me thinks that there is a small issue we are overlooking.

Would you like a sample of the tables to try and play around with? (I have to get this working by Thursday!)

Clemens

 

[MajP]

Although you say it works manually, the only way I can get an eval to return the value from a control on a form is to explicitly define it. Ex:
Debug.Print Eval("forms!frmCategories!description")
Debug.Print Eval("forms('frmCategories').description")

but not
eval("Me.description")
eval ("description")
returns 2482 error.

 

[cpmasesa]

But I am NOT using a control on a form!

I am using it inside a query. Two tables, one with formula template the other with some data that needs to be evaluated and then a query to do the evaluation.

No forms whatsoever

Clemens

 

[MajP]

Your first post mentioned some form calculations so I thought these were control names.

This whole Eval strategy seems flaky. Any chance you want to reconsider? Seems like you have spent a lot of time on an easy problem. Personally I have never used the Eval function because it seems like an excuse for poor coding.

Are the amount of different STATA formulas known and manageable? If they are then just do what I said originally and build your own function. Even if you have 20 different functions a simple select case chould handle that.

If the STATA templates are dynamic and user defined, I would still write my own function to read a STATA template and evaluate it.

 

[MajP]

Your example shows only to types of templates, but maybe there is more.
[probvar]+'='+[correction]-->[probvar]+'='+[probval]
[probvar]+'=""'+[correction]+'""'"-->"[probvar]+'=""'+[probval]+'""'"

If interested here is some refined code. It works fine in a query for me, but I do not know what other requirements there are

Public Function getStata(stataTempType As Integer, probVar As Variant, correction As String, probVal As Variant, batch_a As Variant, sticker_a As Variant) As String
  'Add a template type to table
  '  tempType   probvar_corr                         probvar_probval
  '   1        [probvar]+'='+[correction]            [probvar]+'='+[probval]
  '   2      "[probvar]+'=""'+[correction]+'""'"   "[probvar]+'=""'+[probval]+'""'"
  
  correction = getCorrection(stataTempType, correction)
  probVal = getProbVal(stataTempType, probVal)
  'If there are multiple formulas then you could use a select case here
  getStata = "replace " & probVar & "=" & correction
  getStata = getStata & " if batchNumer_a ==" & wrapStr(batch_a) & "& sticker_Numbera == " & wrapStr(sticker_a)
  getStata = getStata & " & probvar==" & probVal

End Function
Public Function wrapStr(stringVal As Variant) As String
   wrapStr = "'" & stringVal & "'"
End Function


Public Function getCorrection(tempType As Integer, correction As Variant) As String
  If correction = "BLANK" Then
    correction = "."
  ElseIf tempType = 2 Then
    correction = wrapStr(correction)
  End If
  getCorrection = correction
End Function
Public Function getProbVal(tempType As Integer, probVal As Variant)
  If IsNull(probVal) Then
    probVal = "."
  ElseIf tempType = 2 Then
    probVal = wrapStr(probVal)
  End If
  getProbVal = probVal
End Function

here is a test. In a query I would just pass the fields into the furnction

Public Sub testStata()
   Debug.Print getStata(1, "q0117f", "BLANK", "A", "A0230", "FS008870X")
   Debug.Print getStata(2, "q0117f", "A", Null, "A0230", "FS008870X")
   Debug.Print getStata(1, "q0117f", 2, Null, "A0230", "FS008870X")
 
End Sub

and my results:

replace q0117f=. if batchNumer_a =='A0230'& sticker_Numbera == 'FS008870X' & probvar==A
replace q0117f='A' if batchNumer_a =='A0230'& sticker_Numbera == 'FS008870X' & probvar==.
replace q0117f=2 if batchNumer_a =='A0230'& sticker_Numbera == 'FS008870X' & probvar==.

If not good luck with the Eval.

 

[MajP]

Actually, there may be a more efficient and faster method. Each template defines a concatenation string. So if you have ten unique templates, define ten queries each with a unique concatenation string. No nested iif, no vba. Then just just use a join query to bring all ten queries back together.