I have a query which is summing the market value for different asset classes in a portfolio. If the asset class is not there (i.e. the portfolio does not have any municipals, for example), instead of returning zero for the sum, it just leaves off municipals in the row. I would like it to list "municipals" and put zero in for the sum. Not sure how to do that.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Criteria not found in query, need to report "0"
- Thread starter Rasberry
- Start date
Joe4
MrExcel MVP, Junior Admin
- Joined
- Aug 1, 2002
- Messages
- 74,057
- Office Version
- 365
- Platform
- Windows
Yes, you will probably need to do what xenou said in order to return those municipals without matches.
In order to physically show a zero, you probably will also need to use the NZ function, which tells Access what to return in the even of a null (otherwise, it will just return null values for those unmatched municipals).
For example:
MyAmount:NZ([AmountField1],0)+0
The "+0" is just there to coerce the formula to return a value (by default, the NZ function returns a string).
In order to physically show a zero, you probably will also need to use the NZ function, which tells Access what to return in the even of a null (otherwise, it will just return null values for those unmatched municipals).
For example:
MyAmount:NZ([AmountField1],0)+0
The "+0" is just there to coerce the formula to return a value (by default, the NZ function returns a string).
Upvote
0
