Solved

Getting #Type! error in expression builder

Posted on 2016-10-18
5
33 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
[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
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 33

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

740 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