Solved

Getting #Type! error in expression builder

Posted on 2016-10-18
35 Views
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,"#%")
``````

but when I display the continuous form I get:

How can I prevent the error from happening if there are no values in the txtPosCnt or txtNegCnt textboxes?

Thanks!
0
Question by:shogun5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 34

Expert Comment

ID: 41849458
Does this work?

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

LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 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

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

ID: 41849510
Nevermind! Figured it out! Thanks again!
0

LVL 75

Expert Comment

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

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses
Course of the Month2 days, 9 hours left to enroll