Filter a text field for only numeric values

[Guest]

I have a text field that holds both numeric and non-numeric values, basically
scores. Is there a way to filter out all non-numeric values?

What I am doing is using CDbl to convert the text to numeric because I want
to run avg on the values. I then run a make-table query because it seems to
convert the values to numeric correctly. Then I have to use this new table as
the data source for another query and group and do my averages.

For some reason I get a data type mismatch if I try to use my CDbl query as
the source of another query and group and use avg. I would like to bypass the
make table if possible.

Thanks in advance!!
Clint

 

[Guest]

In the criteria of the query put something like:

Not Like "*[A-Z]*"

 

[Guest]

IsNumeric (as criteria)

 

[John Spencer]

If you want to filter out the record entirely you can use the options you've
been given.

If you need to keep the record, but need the value to be null when it is not
a number then try

Field: MyFieldNumber: IIF(IsNumeric([MyField]),Val([MyField]),Null)

Use this calculated field for Sum, Avg, Min, Max, etc.