Im calculating error percentages and if the second number subtracted in the numerator is blank its should actually be 100 %, instead it displays #Error. Is there a way that I can type an iif statement in criteria so that if there is an error I can make it equal to something else like '0' or 'correct'?
IIF Error
- Thread starter Purdue15
- Start date
MSAccessRookie
AWF VIP
- Local time
- Yesterday, 19:41
- Joined
- May 2, 2008
- Messages
- 3,428
I can see two possibilities for your error:
- CWeight = 0
- In this case, an IIf() Statement can be used to replace the error condition.
- CWeight or NWeight are Null
- In this case, Nz() can be used to replace the Nulls with an appropriate default value.
-- Rookie
Ok well the C columns are always going to be completely filled out no matter what. The N columns are the only ones that will be empty and most of them will be empty if there wasnt a change. So a lot of times there will be very few changes. I tried this and it didnt work
Nz([NWeight],'OK')
Nz([NWeight],'OK')
Attachments
Last edited:
MSAccessRookie
AWF VIP
- Local time
- Yesterday, 19:41
- Joined
- May 2, 2008
- Messages
- 3,428
Thank you for providing much better information. The Nz() solution will be a good place to start for you, but your implementation does not appear to be consistant with your proposed usage.
The previous exhibit { Weight: ([CWeight]-[NWeight])/[CWeight]*100 }, shows NWeight being used as a part of a Math Equation. If that is true, then NWeight needs to be a number. Your current exhibit { Nz([NWeight],'OK') } makes NWeight a String. Changing the 'OK' to an appropriate number is what you want to do, although you may need to play with it until you find the correct value.
-- Rookie
Last edited:
