Using AVG in Control Source

[martin DH]

Is it possible to have this as a textbox control source:

 =(Avg([Q4c]))

when the field Q4c is formatted as a text field?

The field Q4c can contain "N/A, 1, 2, 3, 4, or 5" so I think it must be formatted as text due to the "N/A"

But I need to take the average of this field on a report. I tried and it worked once but not again (see post at http://www.thescripts.com/forum/thread768415.html).

How about a better way to find the average of this field? Thanks!
martin

[FishVal]

Hi, martin.

Try the following:

[code=vb]
=Avg(iif([Q4c]="N/A", Null, Val([Q4c]))
[/code]

Though I would recommend to use Number type field and just replace Null values with "N/A" using Nz() function either in ControlSource or in RowSource SQL when displaying single values.

[code=vb]
=Nz([Q4c], "N/A")
[/code]

[code=sql]
SELECT *, Nz([Q4c], "N/A") AS Q4cAlias FROM SourceTable;
[/code]

Regards,
Fish

[missinglinq]

This

=(Avg(Val[Q4c]))

should do. The Val() function will return zero for all fields where "N/A" appears, and Avg() will include these fields when calculating the average.

Linq ;0)>

[martin DH]

Hi, martin.
Though I would recommend to use Number type field and just replace Null values with "N/A" using Nz() function either in ControlSource or in RowSource SQL when displaying single values.

[code=vb]
=Nz([Q4c], "N/A")
[/code]

[code=sql]
SELECT *, Nz([Q4c], "N/A") AS Q4cAlias FROM SourceTable;
[/code]

Thanks, FishVal; I want to look into your recommendation.

I converted the datatype of Q4c to Number which deleted all "N/A" from the table, leaving some null values.

Now, on my report, I would set the control source of the text box that was previously

=(Avg([Q4c]))

to

=(Avg(Nz([Q4c], "N/A")))

Is that correct?
Thanks.

[martin DH]

FishVal and missinglinq,

I actually tried both suggestions and both return the same error: "Data type mismatch in criteria expression."

I'm a little unfamiliar with this aspect - what about writing a query that calculates the average and having that query be the control source for the textbox?

Any ideas are most appreciated - thanks.

[FishVal]

Hi, Martin.

I'd like to explain the logic of what I've suggested.

• you want to calculate average on data set where some values may be absent ("N/A")
• average on text fields definitely has no sence
• if you try to convert your text field to a number, then Val() function will return 0 for "N/A"s thus including them in calculating of average which will cause a wrong result
• on the other hand Avg() will work smoothly with Null values
• so, if you want to stay with a text field. then you need to convert it to a corresponding number with Val() function or to Null if its value is "N/A"
  [code=vb]
  =Avg(IIf([FieldName]="N/A", Null, Val([FieldName]))
  [/code]

• on the other hand you may convert the field to Number as it supposed to have a numeric values
• but in this case, if you want to see Null values as "N/A"s you'll need to convert them using Nz() function

Hope that this makes a sence
Fish

[martin DH]

FishVal,

Thank you very much for breaking down the logic of your response - it always helps to see layperson's terms along-side the code!

I knew taking the average of a text field made no sense, but I wasn't sure how to get around the fact that the fields could include "N/A." So I have done as you suggested and my report is calculating a printing correctly.

Thank you!
martin