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])))
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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)))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.