Solved: Creating Null Value using IIF Statement in Query Builder

[Eric58132]

Hello, easy one here (I think)

I have data that is formatted like this:

March 2010 NULL
March 2010 10
March 2010 0


in a query. With Totals selected, I'm trying to find an average of values, while grouping by the Month/Year. In this instance, I want to ignore the NULL, and bring an average of 5. My suspicion is that I need to create an expression to remove the NULL values, making them blank. I have tried

Expr2: IIf([CoachingEffectiveness]="NULL","",[CoachingEffectiveness])

but am met with a data type mismatch. Am I on the right track here, but have poor formatting, or do I need to try something else?

[hansup]

The SQL aggregate function, Avg(), should ignore Null values, and produce the average of only the non-Null values.

For example, say your Month/Year field is named MonthYear a query like this should give you what you want:

SELECT MonthYear, Avg(CoachingEffectiveness) AS AvgEffectiveness
FROM YourTable
GROUP BY MonthYear;

What type of response are you getting?

[Eric58132]

that's the thing with the Nulls..... it actually says "NULL" in the field name, haha.

[hansup]

Sorry, I totally misunderstood.

But in that case, can you add a WHERE clause to exclude rows which have "NULL" for CoachingEffectiveness?

WHERE CoachingEffectiveness <> "NULL"

[hansup]

And that must mean CoachingEffectiveness is text data type rather than numeric. So you can transform the text values to numbers to produce your average:

Avg(Val(CoachingEffectiveness)) AS AvgEffectiveness

[Eric58132]

Yup, You got it thanks for your help! There are some other averages (where I didn't have this NULL issue) that I wanted to do within the same query but I will just do everything separately and then combine later.


Thanks again.