Expression Editor - avg problem

This is my data set

PersonIdType (varchar 20)

153 NewlyEnrolled

58 8

248 Enrolled

85723

I am trying to show an avg for all the numbers in the ‘Type’ field

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value)))

I get the error “Input string was not in a correct format.”

If I use:

=avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1))

It works and it returns ‘1’

so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function.

any idea what I'm doing wrong?

CDbl returns a double from (typically) a floating point number.

How about trying out CInt or StrConv?

|||

CInt, Cdbl, Cdec, Csng.... all do the same thing.

StrConv is for string manipulation, cant use that to convert to a number

|||

What happens when you simply take out Cdbl?

I suppose you get an error stating that you can't average a varchar field?

Try this:

=CStr(CInt(Fields!Type.Value))

|||

What happens when you simply take out Cdbl?

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

=CStr(CInt(Fields!Type.Value))

The Value expression for the textbox ‘textbox25’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

I also tried

=CInt(CStr(Fields!Type.Value))

Input string was not in a correct format.

|||I just made a new field that held the numbers and did an average on that. Not what I wanted but it worked.