Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Getting #Type! error in expression builder

Dear Experts,

Wondering if you all could help me with this expression:
The following expression is in the control source of txtPercentPos:
=Format((Val([txtPosCnt])/Val([txtPosCnt])+Val([txtNegCnt]))/100,"#%")

Open in new window


but when I display the continuous form I get:
screenshot
How can I prevent the error from happening if there are no values in the txtPosCnt or txtNegCnt textboxes?

Thanks!
0
shogun5
Asked:
shogun5
  • 2
  • 2
1 Solution
 
NorieVBA ExpertCommented:
Does this work?

=Format((Val(Nz([txtPosCnt]))/Val(Nz([txtPosCnt]))+Val(Nz([txtNegCnt])))/100,"#%")

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try

=Format(IIf(Nz([txtPosCnt], 0) = 0, "", (Nz([txtPosCnt], 0) / [txtPosCnt] + [txtNegCnt])) / 100, "#%")

Hope I have the parens in the right place ...
0
 
shogun5Author Commented:
Joe,

This works if txtNegCnt is blank but errors if txtPosCnt is blank as well. Can you assist with check if both fields are blank?
0
 
shogun5Author Commented:
Nevermind! Figured it out! Thanks again!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Cool.  Mainly, you have to prevent Divide By Zero ....and ... Null propagation - hence the Nz() to convert Null to Zero.

Sorry ... I went to bed shortly after that post.
0
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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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