Find fields with only numbers in Text formatted column

[Erick C]

Hi everybody -

I have a problem that I hope someone can help me out with. I have a
spreadsheet that I am importing into Access 2003 to be used for some
totals. Unfortunately, the spreadsheet is not very pretty, it is
created from a pdf-type file that was formatted for printing. I am
using text to columns to get the data broken up with the columns that
I need. But this leaves me with a lot of rows with unnecessary
information and cells with pieces of names snd stuff (like where a
name or date has been split up by the text to columns.
So, I have a certain text column (We will call it Market Value) that
contains dollar amounts that are formatted as either a 0 (not 0.00) or
"x,xxx.xx" as well as fields with blanks and junk from the text to
columns split. What I am looking to do is to make a query that will
return the rows where the Market Value field is a number only, and
ignore anything that is not a dollar value between 0 and
9,999,999,999.99. I have been searching older posts but I have not
found anything that exactly fits what I am looking for.
Some of the garbage fields in this column do contain numbers, like
fileds with dates. So I cannot use a query that ignores or deletes
any alpha in the field and leave the numeric. I need a query that
will only return the fields that have numbers only along with the
period between the dollars and cents, and ignore any filed that is
blank or a mix of alpha and numeric.

If there is anything that does not make sense, or needs further
clarification, please let me know. Any help I can get would be
greatly appreciated.

Thanks.

Erick

 

[John W. Vinson]

I need a query that
will only return the fields that have numbers only along with the
period between the dollars and cents, and ignore any filed that is
blank or a mix of alpha and numeric.

Try a calculated field

IsNumeric([fieldname])

with a criterion of True:

?isnumeric("300")
True
?isnumeric("3,100")
True
?isnumeric("$3,100.00")
True
?isnumeric("3/5/2010")
False
?isnumeric("3A22")
False
?isnumeric(null)
False
?isnumeric("")
False

 

[Erick C]

John -
I will definitely give it a try, thank you for the response.

I need a query that
will only return the fields that have numbers only along with the
period between the dollars and cents, and ignore any filed that is
blank or a mix of alpha and numeric.

Try a calculated field

IsNumeric([fieldname])

with a criterion of True:

?isnumeric("300")
True
?isnumeric("3,100")
True
?isnumeric("$3,100.00")
True
?isnumeric("3/5/2010")
False
?isnumeric("3A22")
False
?isnumeric(null)
False
?isnumeric("")
False