Averaging in Query with null values

[TurboBeagle]

I am querying on a query. The first query is taking out bad data (and replacing it with NULL). The second query is using the TOTALS function, and I am trying to use the AVG of the field. The query says the field is no longer a value field ("DATA TYPE MISMATCH IN CRITERIA EXPRESSION").

The point of replacing the bad data (which are 0's and other garbage data) is so that the garbage data does not affect the average.

I could exclude the records with the bad data, but then I would have to have a query for every field I am doing this to, which is about 50, instead of 1 query.

Any suggestions? Thanks!

 

[neileg]

Just to expand on Pat's point.

If you have garbage in your field, it is likely that this will be a text field anyway. So all of these values will be text. You could add a calculated field that uses the Value() function, e.g.
Newfield: Value(Oldfield)
and then average this calculated field.