Solved

Eval() function and formula templates

Posted on 2008-06-10
42
683 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:cpmasesa
  • 21
  • 20
42 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21754030
String2Quote is a variable that must be evaluated to a string using CStr function

try changing this:
          QuotedStr = Chr(34) & String2Quote & Chr(34)
to this:
         QuotedStr = Chr(34) & CStr(String2Quote) & Chr(34)

Or this::
          QuotedStr =  CStr(String2Quote)
0
 

Author Comment

by:cpmasesa
ID: 21755804
string2quote IS a string

Your suggestion to change to QuotedStr = Chr(34) & CStr(String2Quote) & Chr(34) makes no difference to the original QuotedStr = Chr(34) & String2Quote & Chr(34) I suppose since String2Quote is already a tring

With your second suggestion I lose the enclosing " so instead of getting " blah blah" i just get blah blah (a string that is not enclosed in "

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

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

Removing the call to QuotedStr(), I put this when testing manual coding of the formula template

When I now run the code I get the message:
Runtime error 2482. Microsoft access can not find the name 'ProbVar' you entered in the expression

I tried qualifying the field name with tablename as in table_name.field_name and got the same error message but now it could not find the 'table_name'

Any ideas whats going on here and how to solve this?

Clemens




0
 
LVL 44

Expert Comment

by:GRayL
ID: 21756138
Public Function eval_mmm2(str2evaluate As string)
--------------------------------^--where did the '2' come from?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21756618
CStr is not working because you also need to include it in your function argument as shown below:
CStr is needed in order to pass the string assigned to the variable  str2evaluate, to the Eval  function.
Change this:
                Public Function eval_mmm2(str2evaluate As string)
to this:
               Public Function eval_mmm2(CStr(str2evaluate) As string)
The way you have it, your function is evaluating this string as  "str2evaluate" rather than actual string assigned to the variable.

Also, did you followup on what GRayL told you?  (Hi Ray!)  
0
 

Author Comment

by:cpmasesa
ID: 21757079
GRayL:

The 2 is because its another function, without the call to QuotedStr()

puppydogbuddy:
I get a compile error (Expected: identifier) if I try to do what you say:
Public Function eval_mmm2(CStr(str2evaluate) As string)

VBA doesn't seem to like the call to cstr() here.

Clemens
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21757160
Hmmm.....you may be right about CStr .......I will have to look into it......in the meantime, instead of using CStr as a wrapper for the variable before passing it to the function (as I suggested above) try using it in the function in place of Eval as shown below.  Eval and CStr are very similar when it involves strings.

Public Function eval_mmm2(str2evaluate As string)
 eval_mmm = CStr(str2Evaluate)       "<<<<<<<<<<<<<
End Function
0
 

Author Comment

by:cpmasesa
ID: 21757183
But that would not evaluate the formula template contained in Str2Evaluate, which incidentally is itself a string?

I thought that we needed to use Eval() so that the formual can be evaluated.

Am I missing something?

Clemens
0
 

Author Comment

by:cpmasesa
ID: 21757280
SO, I tried your suggestion.

When Str2Evaluate is read from a field, as I would like it to be - Eval_mmm2(Formula2Use) - where Formula2Use is a field containng my formula template, your solution just shows the formula template stored in the table. In this case I get the result ProbVar & " = " & Correction

When 'hard coding' the formula template - Eval_mm2(ProbVar & " = " & Correction) - I get the results I expect

Could it be that Access does not consider ProbVar and Correction as strings since they are field names?

I can not figure out why it does not work when the formula is picked up from a field but works when the formula is hardcoded.

Clemens
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21758554
Clemens,
What happens when you do the following?

Public Function eval_mmm2(str2evaluate As string)
 eval_mmm = Eval(CStr(str2Evaluate))       "<<<<<<<<<<<<<
End Function
0
 

Author Comment

by:cpmasesa
ID: 21813968
puppydogbuddy

I tried what you suggested but it does not solve the problem

Thanks for trying
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21817687
Don't give up just yet......I think this link may have your answer....read it in its entirety.
           http://msdn.microsoft.com/en-us/library/aa172212(office.11).aspx

excerpt from the reference:
You can use the Eval function in a calculated control on a form or report, or in a macro or module. The Eval function returns a Variant that is either a string or a numeric type.

The argument stringexpr must be an expression that is stored in a string. If you pass to the Eval function a string that doesn't contain a numeric expression or a function name but only a simple text string, a run-time error occurs. For example, Eval("Smith") results in an error.

See this link for an example of a work around the limitations of the Eval function.

Hope this helps.

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21817693
oops! forgot the link:
      http://bytes.com/forum/thread191343.html
0
 

Author Comment

by:cpmasesa
ID: 21817767
Thanks puppydogbuddy:

I will look at the link and come back to you
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 21838430
looks like other people are having trouble with the eval function .....see this link

http://www.access-programmers.co.uk/forums/showthread.php?t=33931
0
 

Accepted Solution

by:
cpmasesa earned 0 total points
ID: 22065934
Thank you al for your help.

I have not yet been able to find a solution to my problem despite all your suggestions

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22067952
Clemens,
What happens when you do the following? I just realized str2Evaluate has to be defined as a variant data type, not a string.

Public Function eval_mmm2(str2evaluate As Variant)   '<<<<<<<<<<<
 eval_mmm = Eval(CStr(str2Evaluate))       "<<<<<<<<<<<<<
End Function
0
 

Author Comment

by:cpmasesa
ID: 22068139
puppydogbuddy,

I tried what you suggested and I get Runtime Error 2482. Microsoft Access cann't find the name 'probvar' you entered in the expression.

Any ideas as to why this might be so?

Clemens
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22068283
Did you use the following syntax??
        eval_mmm([ProbVar]&" == " & [ProbVal])

Try it this way:
        eval_mmm("[ProbVar]= " & [ProbVal])
Or this way:
      eval_mmm("[ProbVar]= '" & [ProbVal] & "'")
Or this way:
             eval_mmm(CStr("[ProbVar]= " & [ProbVal]))

0
 

Author Comment

by:cpmasesa
ID: 22068711
I am not sure what yo mean.

my "rules" is [probvar] & ' = ' & [probval].  I also have other rules:
[probvar] & ' = ' & [correction] and [probvar] & ' = "' & [correction] & '"' and [probvar] & ' = "' & [probval] & '"' which are all kept in a table.

The fields containing these rules are named probvar_corr and probvar_probval

So when I call eval_mmm2() I pass the field name and not the syntax (eg [probvar] & ' = ' & [probval])  as the parameter.

I however tried changing my syntax in the rules table with no success

Maybe the problem is because I am reading the syntax from a field as opposed to typing it into the function (?!)


0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22069374
0
 

Author Comment

by:cpmasesa
ID: 22069454
Ok.

So how should I do it given the setup I just described?

The "formulas" that I need to be evaluated are picked up from a table field, say the field is called "rulez"

so I expect eval(rulez) to evaluate the "formula"
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22069844
As previously stated, Access syntax for a parameter that returns a numeric data type:
 eval_mmm("[ProbVar]= " & [ProbVal])
For text data types, it is this way:
      eval_mmm("[ProbVar]= '" & [ProbVal] & "'")

idea!! why can't you use the Switch function to replace your syntax with Access.
    http://www.techonthenet.com/access/functions/advanced/switch.php

eval_mmm(Switch( [probvar] & ' = ' & [probval],"[ProbVar]= '" & [ProbVal] & "'","[ProbVar]= " & [ProbVal]))  
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22069951
PS
Make sure you changed the data type of your variables to variant as discussed earlier today.
0
 

Author Comment

by:cpmasesa
ID: 22071352
I tried what you told me but i got the message that probval  can not be found (error message 2482 as previously)

So i got creative and substituted 2 for probval, assuming that probvar had a value of 2.

i got" [probvar] = 2" as the result! [probvar] was not evaluated.

Would you be willing that i send you a copy of the data (3, just few records) so that we further try to work this out?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22072415
Try one more thing........if this does not work, then send to address in my profile. I  only have Access version 2000.

eval_mmm("[ProbVar] = [ProbVal]")
or
eval_mmm(Switch( [probvar] & ' = ' & [probval],"[ProbVar] = [ProbVal]"))    
0
 

Author Comment

by:cpmasesa
ID: 22073326
Tried,  did not work.  Gave me result as [ProbVar] = [ProbVal] but not the evaluated equivalent.  I am preparing a small subset of data to send to you in a few minutes
0
 

Author Comment

by:cpmasesa
ID: 22074149
I have been playing around trying to solve this and made a startling discovery.

I simplified the problem, you need some data and its easier/safer for you to create (assuming that as me, you really want to solve this)

create a table with on field call probvar (text field). call the table testdata (or whatever)
enter a few rows of data, some numbers, some alphabetic and some alphanumeric

i used the following data:
1
2
3
abc
xyz
klm
abc123
xyz909
99

create function in a module as follows: (as you suggested
**********************************************************
Public Function eval_mmm2(str2evaluate As Variant)
 eval_mmm2 = Eval(CStr(str2evaluate))
End Function
************************************

create a query on the table testdata, return the field probvar and an additional field solve which has evaluated:eval_mmm2(probvar)

run the query ---> you get errors!

edit the query and change the second field to be
evaluated:eval(probvar)

run the query --> results for records with number in the field probvar but error for records with non numeric value in field probvar

Does this suggest that eval() on works for numeric values?

Any ideas how to have the values returned for the non numeric fields?

(I just increased the point value of this question!)

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22074514
Eval only works for strings......that is why I used the  CStr  function to convert each str2Evaluate to a string, whether it is a text or numeric.....but eval will not evaluate literal strings like Smith because there is nothing to evaluate.
0
 

Author Comment

by:cpmasesa
ID: 22076224
ok.

So I suppose that I am using the wrong function in trying to get what I want, which is to pass a "formula" stored as a string (eg "[probvar] == [probval]) in a table field as described previously and have access "evaluate" that and give me an answer such as "q0103a == 13"

as long as the fields probvar and  probval have numeric values eval() will "evaluate" but not for string values.  So this is the wrong function for what I want to happen?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22078640
the eval function is supposed to evaluate any function or expression that be presented to it as a properly formatted string, except for literal strings like "smith", for which there is nothng to evaluate...so I think Eval is the correct function. Either the string is not being formatted correctly to the function, or the function does not do what it is supposed to do.

I jus discovered that there is a CVar function  that converts a strng to a variant.  Try this:
                            eval_mmm2 = Eval(CVar(CStr(str2evaluate)))
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22078684
also try it ths way:
eval_mmm2 = Eval(CStr(CVar(str2evaluate)))
0
 

Author Comment

by:cpmasesa
ID: 22078835
Tried both of them and got the same error as before in both cases

why do we take a variant, str2evaluate, convert it to a string, cstr() and then convert it back to a variant, cvar() ??

str2evaluate and cstr(str2evaluate) are the same, when you debug the code, so there seems to be no benefit in doing cstr(str2evaluate) even though str2evaluate is a variant (try it and you will see)

what is it that we are missing?  I have tried formating my 'formula' (expression) in many differnet ways with no success.

hence the decision attempt to use eval() with just an expression that is only one field name.  and here we discovered that it works fine if the contents of that field are numeric but fails otherwise.

i suspect that therein lies the solution to our problem.  just can not seem to fine the solution!

-
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22078936
A variant will handle nulls, while other functions won't. ....so I was trying to see if nulls were influencing the result. Sometimes you have to play around with dfferent combinations of functions to achieve the right result.  
0
 

Author Comment

by:cpmasesa
ID: 22079095
ok. we have played around a lot and I still do not get where we are going wrong.

any ideas why eval() works for number but not strings in the field name? that to me seems to be where the solution lies
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22079774
i think because of different syntax (numeric vs. text) previously discussed.
numeric:
"[FieldName] = " & XXX    

Text:
"[FieldName] = '" & XXX & "'"  
0
 

Author Comment

by:cpmasesa
ID: 22079952
yes but we tried both variants of the syntax and it never worked for the text values.

have you tried re creating the problem? then you must have also seen that none of the variations works for text values
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22084321
no I have not replicated the problem.......have not had the time or the need to.  Am just trying to help you.

have you tried to assign [ProbVal] to a variable and syntax it that way before bringing it into the eval function?....like this.
------text string syntax---------
Dim strProbVal As Variant
strProbVal = Forms!yourForm![ProbVal].Value
str2evaluate = " [ProbVar] = '"  & strProbVal & "'"
eval_mmm2 = Eval(CStr(str2evaluate))

-----------numeric string Syntax ---------------
Dim strProbVal As variant
strProbVal = Forms!yourForm![ProbVal].Value
str2evaluate = "[ProbVar] = "  & strProbVal
eval_mmm2 = Eval(CStr(str2evaluate))
__________________________________________________
Also, see this link by Microsoft for some good examples of how the eval function syntax is implemented.
    http://office.microsoft.com/en-us/access/HA012289421033.aspx

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22084350
in text version above, eliminate the extra space
change this:
                 str2evaluate = " [ProbVar] = '"  & strProbVal & "'"

to this:      str2evaluate = "[ProbVar] = '"  & strProbVal & "'"
0
 

Author Comment

by:cpmasesa
ID: 22086435
I can not use that approach as I am NOT using a form to do this.  

I am using SQL to join 3 tables and picking the relevant expression to be evaluated from a field in on of the 3 tables and attempting to get the expression evaluated

0
 

Author Comment

by:cpmasesa
ID: 22086962
The original SQL I used was :

SELECT Checks.ProbVar, Checks.ProbVal, Checks.Correction, Eval([Syntax_if]) AS stata_if, Eval([syntax_replace]) AS stata_replace
FROM (CorrRules INNER JOIN DataDict ON CorrRules.vartype = DataDict.vartype) INNER JOIN Checks ON DataDict.varname = Checks.ProbVar;

With your help I have since changed the calll to Eval() to be Eval_mmm2()

The field syntax_if has [probvar] & ' == ' & [probval] , among other values. The field syntax_replace has [probvar] & ' = ' & [correction] among other values.

I have changed the expressions as you suggested many many times but all variations failed

I have attached a subset of my data 13 out of 50k+ records for you to better see what I am trying to do and what my problem is.

(To cancel when you get an error, in the VBA editor click the triangle x2)
(Also trying running it with just eval() instead of eval_mmm2())
(The .mdb is in Access 2000 format -  thats what you said you had)


MkV1-FS-Checks-BE.zip
0
 

Author Comment

by:cpmasesa
ID: 22087677
At last!  I persevered and got the solution to my problem!

Quite simple really, once I figured out how, with the help of a colleague here at the office.

I should have used REPLACE.  I did, and it works perfectly.

My SQL is now:

SELECT Checks.SerialNo, Checks.ProbVar, Checks.ProbVal, Checks.Correction, Replace(Replace([syntax_replace],'[probvar]',CStr([ProbVar])),'[correction]',CStr([Correction])) AS stata_replace, Replace(Replace([syntax_if],'[probvar]',CStr([ProbVar])),'[probval]',CStr([probval])) AS stata_if
FROM (CorrRules INNER JOIN DataDict ON CorrRules.vartype = DataDict.vartype) INNER JOIN Checks ON DataDict.varname = Checks.ProbVar;

Thank you for all your help
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 22087812
congrats!  Persistence pays off.  Glad you got it resolved.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now