• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 55
  • 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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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