iif statement return a blank field

[Nancy2021]

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))

 

[Joe4]

Welcome to the Board!

If this for an Aggregate Query?
Can you switch your query to SQL View, and copy and paste the SQL code here?

 

[Nancy2021]

Welcome to the Board!

If this for an Aggregate Query?
Can you switch your query to SQL View, and copy and paste the SQL code here?

Thanks for the quick reply!

SELECT Studentattendanceall.School, StudentDemographics.FirstName, StudentDemographics.LastName, Sum(IIf(Month([uploaddate])=10,[absentdays],0)) AS Oct, Sum(IIf(Month([uploaddate])=11,[absentdays],0)) AS Nov, StudentDemographics.StudentID, Studentattendanceall.NoRisk, Studentattendanceall.AtRisk, Studentattendanceall.Chronic, Studentattendanceall.PresentDays, Studentattendanceall.AbsentDays, Studentattendanceall.DaysEnrolled, Studentattendanceall.PercentAbsent, Sum(IIf(Month([uploaddate])=10,[percentabsent],0)) AS OctPercentAbsent, Sum(IIf(Month([uploaddate])=11,[percentabsent],0)) AS NovPercentAbsent, Studentattendanceall.Grade, StudentDemographics.Ethnicity, StudentDemographics.Gender, (IIf(Month([uploaddate])=11,[Chronic],"")) AS NovChronic, (IIf(Month([uploaddate])=10,[Chronic],"")) AS OctChronic
FROM StudentDemographics INNER JOIN Studentattendanceall ON StudentDemographics.StudentID = Studentattendanceall.StudentID
GROUP BY Studentattendanceall.School, StudentDemographics.FirstName, StudentDemographics.LastName, StudentDemographics.StudentID, Studentattendanceall.NoRisk, Studentattendanceall.AtRisk, Studentattendanceall.Chronic, Studentattendanceall.PresentDays, Studentattendanceall.AbsentDays, Studentattendanceall.DaysEnrolled, Studentattendanceall.PercentAbsent, Studentattendanceall.Grade, StudentDemographics.Ethnicity, StudentDemographics.Gender, (IIf(Month([uploaddate])=11,[Chronic],"")), (IIf(Month([uploaddate])=10,[Chronic],""));

 

[Joe4]

One way is to create another query based off of this query, and do something like:

IIf([Oct]=0,"",[Oct])

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.

Video: Use conditional formatting on reports - Microsoft Support

Conditional formatting allows you to apply different formatting to individual values on Access desktop reports, either based on the value itself, or on a calculation that includes other values.

support.microsoft.com

 

[Micron]

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)

 

[Nancy2021]

Thank you, I'll have to think about how best to do this.

 

[petertenthije]

Could you try this?

IIf(Month([uploaddate])=10,Sum([absentdays]),null)) AS Oct,