Divisible by zero error

Posted on 2014-03-24
Last Modified: 2014-03-24
I want the percent used section of the data to have this ----- instead of #Div/0 and or #Num if dividing by zero and below is the formula I am using.

%Used: Format(Nz([YTD],0)/Nz([AnnualBudget],0),"Percent")


Budget      MTD      YTD      %Used

0.00      0.00      441,000.00      #Div/0!

0.00      0.00      0.00      #Num!

1,800.00      21.81      372.67      20.70%
Question by:Kdankwah
  • 3
  • 2
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 500 total points
ID: 39950357
Since you are using the Format ( ) function, which returns a string, you could simply use:

%Used: IIF(NZ([AnnualBudget],0) = 0, "----", Format(Nz([YTD],0)/[AnnualBudget],"Percent"))

Author Comment

ID: 39950370
Thanks so much

Author Comment

ID: 39950371
What will be if I am using a formula that will return a number string.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39950848
you could still use the iif() function, but cannot embed a string in an otherwise numeric field, so something like:

iif(isnull([field name]), null, [other field]/[field name])

Author Comment

ID: 39950904

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 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