• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

How do I stop a form displaying #Error in text boxes

Hi there.  I have a form which uses some text boxes to display the latest date as derived from a field elsewhere in the form.  However, when the form is first opened (and displays a blank record), they display #Error, presumably because they're trying to work out a calculation for an empty record.  Is there any way of making the text boxes not display the #Error thing?

For example by the way, one of the text boxes is called Text44 and its control source is:

=(Nz(DMax("[DateCompletedAnnual]","tblAnnual","IDClient= " & [IDClient])))
1 Solution
You need to supply an id value that will not be found in the record- such as 0.

=Nz(DMax("[DateCompletedAnnual]","tblAnnual","IDClient= " & nz([IDClient],0)))
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

Pete's approach of avoiding the error in the first place is the best course of action.

You can also use the Wrap an expression in the IsError() function

IIF(Iserror(SomeExpression),"Error Detected",SomeExpression)

But again, here your question was how to Stop the error in the first place, so go with Pete's post.
Besides, there are a few "Errors" that IsError just cannot detect, so it is not ideal for all conditions.

Another thing to investigate would be loading this value from code.
Using code you have a lot more options when dealing with "Errors"
(Error Handling, Validation, ...etc)


Note that without the valueifnull value in the Nz() function, it will produce a zero length string for null values. This is what is creating the error.  It would pay to have a good read of the Help file - click Alt+F11 to get to the VBA Editor and in Help type - Nz function
fernandowebAuthor Commented:
Thanks to boag2000 and GRayL too for your help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now