Eval function question

[J Welsby]

Simple question I think for those more familiar with this function!

Have a form with a text box which holds a string. A button on the form has
one line of code attached to it's Click event: msgbox
Eval(forms!frmMainForm!txtboxString)

If the text box contains something as simple as: 5*5 ...
....all is good and the msgbox returns 25

If the text box contains something like: if 5>2 then 123 else 456 ...
....an error is returned with the message "Microsoft Access can't find the
name 'if' you entered in the expression

Really stumped here. Have read the Help section on use of the Eval function
but it has not helped

Any ideas?! Hoping this is an easy one for someone who is more familiar
with this function

Thanks in advance
James

 

[Klatuu]

if 5>2 then 123 else 456 will not work
This will
strFormula = "IIf(5>2,123,456)"
Eval(strFormula) will return 123

 

[Marshall Barton]

Simple question I think for those more familiar with this function!

Have a form with a text box which holds a string. A button on the form has
one line of code attached to it's Click event: msgbox
Eval(forms!frmMainForm!txtboxString)

If the text box contains something as simple as: 5*5 ...
...all is good and the msgbox returns 25

If the text box contains something like: if 5>2 then 123 else 456 ...
...an error is returned with the message "Microsoft Access can't find the
name 'if' you entered in the expression

Really stumped here. Have read the Help section on use of the Eval function
but it has not helped

Any ideas?! Hoping this is an easy one for someone who is more familiar
with this function

If is a VBA statement. The Eval function can only evaluate
constants, open form references and builtin and user defined
public functions in standard modules.

In your case, use the expression service's IIf function.
Set the text box's value to:

IIf(5>2, 123, 456)

 

[J Welsby]

Perfect. Many thanks

If is a VBA statement. The Eval function can only evaluate
constants, open form references and builtin and user defined
public functions in standard modules.

In your case, use the expression service's IIf function.
Set the text box's value to:

IIf(5>2, 123, 456)

 

[Guest]

... or re-write it as an arithmetic expression:

-eval(" ((5>2)*123) + ((5<=2)*456)" )

)smirk(

(david)

 

[chris.nebinger]

Oh, sure, get technical.....

But...... True evaluates to -1, so the result is -123.

eval(" (ABS(5>2)*123) + ((5<=2)*456)" ) is more accurate.


Chris

 

[Marshall Barton]

Oh, sure, get technical.....

But...... True evaluates to -1, so the result is -123.

eval(" (ABS(5>2)*123) + ((5<=2)*456)" ) is more accurate.

I think you failed to notice that David has a minus sign in
front of the Eval(...)

 

[J Welsby]

Still struggling with the Eval function, almost there...one last question if
I could pls!

I have a custom function call FMin (and conversely another called FMax)
which when fed a string will return either the min of the string

i.e. feed the FMin the following string:
(10,20,30)
and it will return 10. Easy

Now, my strings are actually mathematical strings such as:
x=min(10,20,30)

I use the Replace function to remove the 'x=' and to change the min to
'FMin'...so, how do I now evaluate this modified string which looks like:
FMin(10,20,30)

If this string is simply in a text box on a form and I have the following
code:
Eval(forms!frmMainForm!txtboxstring) on the Click event of a button, I
receive the following error...

Run-time error '2439'
The expression you entered has a function containing the wrong number of
arguments

Very new to VBA but seems I am almost there...can anyone help out? Cheers
James

 

[Douglas J Steele]

How is your function declared?

 

[J Welsby]

Doug, have pasted in the module code below. Scroll down a bit and you can
see the FMin function that I am trying to use. Thanks in advance for any
insight
James

Option Compare Database 'Use database order for string comparisons
Option Explicit

'
' These functions provide similar functionality to
' Domain functions, except, the values are passed
' in a string.
'
' e.g. Debug.Print FAvg("1,2,3,4,5")
'
' For summing fields, you can do the following in a controlsource:
' =FSum("|Field0]|,|[Field1]|,|[Field2]|")
'
' Nulls are ignored as they are in Domain functions
' Items must be separated by commas
'

Dim FFieldCount As Double


Function FAvg(S)
'
' Returns the average of valid numbers in the list supplied
'
Dim Tot As Double, i As Integer, x, NullCount As Integer
FGetNumberCount S
Tot = 0
NullCount = 0
For i = 1 To FFieldCount
x = FGetNumber(S, i)
If IsNumeric(x) Then
Tot = Tot + Val(x)
Else
NullCount = NullCount + 1
End If
Next i
If NullCount = FFieldCount Then
FAvg = Null
Else
FAvg = Tot / (FFieldCount - NullCount)
End If
End Function

Function FCount(S)
'
' Returns a count of valid numbers in the list supplied
'
Dim NullCount As Integer, i As Integer, x
FGetNumberCount S
NullCount = 0
For i = 1 To FFieldCount
x = FGetNumber(S, i)
If IsNumeric(x) Then NullCount = NullCount + 1
Next i
FCount = NullCount
End Function

Function FGetNumber(S, Item As Integer) As String
'
' Returns the number at "Item" position
' Assumes you've already run FGetNumberCount
'
Dim SPos As Long, EPos As Long, WordCount As Long, x
If VarType(S) <> 8 Then
FGetNumber = ""
Else
SPos = 1
If Item > FFieldCount Or Item < 1 Then
FGetNumber = ""
Else
WordCount = 1
Do While WordCount < Item
If Mid(S, SPos, 1) = "," Then WordCount = WordCount + 1
SPos = SPos + 1
Loop
EPos = SPos
Do While Mid(S, EPos, 1) <> "," And EPos <= Len(S)
EPos = EPos + 1
Loop
x = Eval(Mid(S, SPos, EPos - SPos))
If IsNumeric(x) Then
FGetNumber = x
Else
FGetNumber = ""
End If
End If
End If
End Function

Sub FGetNumberCount(S)
'
' Counts the number of items but not generic for fields
' since [abc,def] would register as two separate items.
'
Dim i As Long
If VarType(S) <> 8 Then
FFieldCount = 0
Else

S = Replace(S, "(", "") 'added to remove any brackets in function string
being passed
S = Replace(S, ")", "") 'added to remove any brackets in function string
being passed

FFieldCount = 1
For i = 1 To Len(S)
If Mid(S, i, 1) = "," Then FFieldCount = FFieldCount + 1
Next i
End If
End Sub


Function FMax(S)
'
' Returns the Maximum number in the list supplied
'
Dim Max As Double, i As Integer, AValue As Integer, x
FGetNumberCount S
If FFieldCount = 0 Then
FMax = Null
Else
AValue = False
For i = 1 To FFieldCount
x = FGetNumber(S, i)
If IsNumeric(x) Then
If AValue Then
If Val(x) > Max Then Max = Val(x)
Else
Max = Val(x)
AValue = True
End If
End If
Next i
If AValue Then
FMax = Max
Else
FMax = Null
End If
End If
End Function

Function FMin(S)
'
' Returns the Minimum number in the list supplied
'
Dim Min As Double, i As Integer, AValue As Integer, x
FGetNumberCount S
If FFieldCount = 0 Then
FMin = Null
Else
AValue = False
For i = 1 To FFieldCount
x = FGetNumber(S, i)
If IsNumeric(x) Then
If AValue Then
If Val(x) < Min Then Min = Val(x)
Else
Min = Val(x)
AValue = True
End If
End If
Next i
If AValue Then
FMin = Min
Else
FMin = Null
End If
End If
End Function

 

[chris.nebinger]

I see what you are doing....

The rest of the functions need a little tweaking as well. You could
also, with a little checking, change your FMIN function to allow for
either arrays or comma delimited string values.

If you are going to pass the S argument as a string, then your code
should look like:
Function FAvg(S)
'
' Returns the average of valid numbers in the list supplied
'
Dim Count As Integer
Dim Counter As Integer
Dim Total As Double
Dim Args() As String
Args = Split(S, ",")
Dim NullCount As Integer
For Counter = 0 To UBound(Args)
If IsNumeric(Args(Counter)) And Not IsMissing(Args(Counter)) And
Not IsEmpty(Args(Counter)) Then
Total = Total + Args(Counter)
Else
NullCount = NullCount + 1
End If
Next Counter
FAvg = Total / (Counter - NullCount)
End Function

If you are going to pass it as a param array:

Function FAvg(ParamArray S())
'
' Returns the average of valid numbers in the list supplied
'
Dim Count As Integer
Dim Counter As Integer
Dim Total As Double
Dim NullCount As Integer
For Counter = 0 To UBound(S)
If IsNumeric(S(Counter)) And Not IsMissing(S(Counter)) And Not
IsEmpty(S(Counter)) Then
Total = Total + S(Counter)
Else
NullCount = NullCount + 1
End If
Next Counter
FAvg = Total / (Counter - NullCount)
End Function