I am using the following in a query. Instead of returning "0" when the statement is negative, I would like a blank field. Is there a way to do this? Oct: Sum(IIf(Month([uploaddate])=10,[absentdays],0))
One way is to create another query based off of this query, and do something like:
Rich (BB code):
However, you need to be aware of the repurcussions of replacing zeroes with blank values - those are text values and could render this field unusable if you need to perform other math operations on it.
If you are trying to hide zero values on a Report, you might be better off using Conditional Formatting to suppress those zeroes.
AFAIK, you can't use Sum function with text or zls (zero length strings) so you would have to default to null when the test is false. Null + anything else will return Null, so I don't know if that will work for you. Perhaps try Sum(IIf(Month([uploaddate])=10,[absentdays],Null)