Issue with NZ function - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Issue with NZ function

Lisa Difiore

4 years ago

I am working in a form that has several sub-forms. Each sub-form has a total and I am trying to add a grand total of those fields. When there are records in every sub-form it works fine. When there is a sub-form without data I am getting an error. In the grand total field I tried wrapping each subtotal in an NZ function. When I do this and do not include a value if null argument I get a #Size! error. When I include a value if null argument of 0 I get a #Type! error. All of the fields are formatted as currency with 0 decimal places. I will try to attach examples of my formula.

	Lisa Difiore OP @Reply 4 years ago

	Randy Wehnes @Reply 4 years ago
If i am understanding what you are trying to do it sounds like a pivot table might be the best way to go here. You can do pivot tables in Access.

	Adam Schwanz @Reply 4 years ago
You need the 0s there to be replacing the null. Are all of those number fields? Or are any short text?

	Randy Wehnes @Reply 4 years ago
One clarification on my reply. Actual pivot tables in Access are no longer an option (goes to show how often I do them, it stopped being there in 2013). You could try a crosstab query.

	Lisa Difiore OP @Reply 4 years ago

	Lisa Difiore OP @Reply 4 years ago
Randy, thank you for the suggestion. I will try a crosstab query.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View