How do I eliminate the #error result in an access equation?

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
914
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
I have a Query which shows info from a table, tbl.RawData.

And within this query I have a few fields which perform simple equations with the info taken from the table.

One of the fields with an equation gives me the #error result when it has a row with zero results in them. The zero result will eventually be populated with a value, but until that happens I get #error.

The equation I put in the field CostPerSqFt is as follows:

nz([Phase1and2])/([TotalSquareFt])


there will be times when the field Phase1and2 will be empty and also TotalSquareFt will be empty.....sometimes they will both be empty and other times it will be one or the other......in the end they will BOTH be populated but but during that time before there's a 0.00 result in the two fields (Phase1and2 and TotalSqFt).

The ones where they (the two fields mentioned above) are not both populated I get the #error in the CostPerSqFt field.

Is there a way to make the CostPerSqFt field value be 0.00 until both other fields are populated with a value?
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I don't think you are using the NZ function properly. Note that it has two arguments. Perhaps this is what you are after:

NZ([Phase1and2]/[TotalSquareFt],0)
 
Upvote 0
I tried your formula and It's still giving me the #error result.

but thank you for your response!
 
Upvote 0
You could try:

NZ([Phase1and2],0)/NZ([TotalSquareFt],1)

Are you sure that none of these underlying values have errors in them?
 
Upvote 0
Positive.....the fields it is calculating have either 0.00...or a number value.....or completely blank.

I tried that next formula you posted and still getting the #error
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top