Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Getting #Type! error in expression builder

Posted on 2016-10-18
5
Medium Priority
?
48 Views
Last Modified: 2016-10-19
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
Comment
Question by:shogun5
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 41849458
Does this work?

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

Open in new window

0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 41849469
Try

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

Hope I have the parens in the right place ...
0
 

Author Comment

by:shogun5
ID: 41849487
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
 

Author Closing Comment

by:shogun5
ID: 41849510
Nevermind! Figured it out! Thanks again!
0
 
LVL 75
ID: 41850609
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question