How do I query "where isNumeric(col), and col > 0" in access

Question

I am in the process of cleaning up some data in an access database where a text attribute that should be numeric and >0 contains non numeric data or numbers that are <0.

Because Access does not have short circuiting, I cannot do a

create table cleaned_up as
select * from a_mess
where isNumeric(col) and col >0

Is there a workaround short of

create table cleaned_up as
select * from (
  select * from a_mess
  where isNumeric(col)
)
where col > 0

Answer 1

Focus on just the SELECT piece to start ...

SELECT *
FROM a_mess
WHERE
        IsNumeric(col)
    AND Val(Nz(col, 0)) > 0;

Then you can adapt that query as a SELECT ... INTO statement (what the Access UI calls a "make table" query) to persist the result set as a table ...

SELECT *
INTO cleaned_up
FROM a_mess
WHERE
        IsNumeric(col)
    AND Val(Nz(col, 0)) > 0;