Eval() #Error

[Kevin Sprinkel]

I am simulating an Excel spreadsheet, wherein the user may
enter a number or a formula into a textbox on a continuous
form. The adjacent cell, txtResult, calculates the
result. Its control source is:

=Eval(Nz([txtFormula]))

The BeforeUpdate event of txtFormula assures that a valid
formula has been entered.

Everything works fine, except that before a formula has
been entered in a new record, txtResult
displays "#Error". I don't understand why--doesn't the Nz
call convert a null to zero? I would therefore expect
txtResult to display zero.

Someone, please let me know where I've gone astray.

TIA
Kevin Sprinkel

 

[Gerald Stanley]

The help file indicates that the Nz function would return a
zero length string when the context of the first parameter
indicates a string and the second parameter is not
provided. I would advise giving =Eval(Nz([txtFormula], 0))
a try.

Hope That Helps
Gerald Stanley MCSD

 

[Allen Browne]

Nz() will not be able to interpret the forumla.

Perhaps:
=IIf([txtFormula] Is Null, 0, Eval([txtFormula]))

 

[Kevin Sprinkel]

Thanks, Allen; that was my problem.

 

[Kevin Sprinkel]

Thanks, Gerald. Your solution also works.