Finding ONLY numbers or ONLY letters in a field

[picmeup]

What is the quickest way to find ONLY numbers in a field that has
alphanumeric? For example, a field that has A12345G and 123456 - if I only
want those values with numbers only, I believe that the expression, Like "*#"
And Like "#*" - takes care of this. Is there an easier way? How about the
opposite (any value that contains a letter, but no values with ONLY numbers)?
Thanks.

 

[Seth Schwarm]

I don't know if you are using the filter field in a query or filtering
functionality in a form but this should work either way.


To show you those fields which are all numbers (no text):
IsNumeric(YourFieldNameHere)

To show you those fields which contain text: Not IsNumeric(YourFieldNameHere)

Seth Schwarm

 

[picmeup]

fyi I tried this but it did not work - I am guessing because the field is
indexed as "text" and not "number?" I am using the Design view in Access 2003
and placing the criteria under the appopriate field (barcode in this case) -
if that clears anything up. I realize the expression I used, Like "*#" And
Like "#*" will still find anything that has a letter inside the value. So my
expression is really saying, "find me any values that do not begin or end
with a letter." Any other suggestions on weeding out all letters? Thanks.

 

[Seth Schwarm]

I checked it out and it works flawlessly. Perhaps we are not speaking the
same language so to speak.

I built a query added the text data type field to the grid, then added a
calculated field of Expr1: IsNumeric([Text1]). When you run the query it
shows the original value held in the text data type field and a -1 for True
or a 0 for False in the calculated field.

From here you can permanently apply a filter in the design of the query or
if you are wanting to look at your data in a table type view or form view,
then show the calculated field and apply a filter of True or False to see
your results.

Hope this helps. Reply back if you need additional help.

Seth Schwarm

 

[John W. Vinson]

What is the quickest way to find ONLY numbers in a field that has
alphanumeric? For example, a field that has A12345G and 123456 - if I only
want those values with numbers only, I believe that the expression, Like "*#"
And Like "#*" - takes care of this. Is there an easier way? How about the
opposite (any value that contains a letter, but no values with ONLY numbers)?
Thanks.

Actually this criterion will find all cases that both begin and end
with a digit - 3AAAAAA5 would match.

If you want to find all cases where the field consists ONLY of seven
numeric digits, use

LIKE "[0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

To find values which contain a letter anywhere in the string use

LIKE "*[A-Z]*"

or to exclude values containing a letter

NOT LIKE "*[A-Z]*"


John W. Vinson [MVP]

 

[picmeup]

Thanks so much for your help.

I checked it out and it works flawlessly. Perhaps we are not speaking the
same language so to speak.

I built a query added the text data type field to the grid, then added a
calculated field of Expr1: IsNumeric([Text1]). When you run the query it
shows the original value held in the text data type field and a -1 for True
or a 0 for False in the calculated field.

From here you can permanently apply a filter in the design of the query or
if you are wanting to look at your data in a table type view or form view,
then show the calculated field and apply a filter of True or False to see
your results.

Hope this helps. Reply back if you need additional help.

Seth Schwarm

fyi I tried this but it did not work - I am guessing because the field is
indexed as "text" and not "number?" I am using the Design view in Access 2003
and placing the criteria under the appopriate field (barcode in this case) -
if that clears anything up. I realize the expression I used, Like "*#" And
Like "#*" will still find anything that has a letter inside the value. So my
expression is really saying, "find me any values that do not begin or end
with a letter." Any other suggestions on weeding out all letters? Thanks.