How to find records that only contains digits?

M

Morris

Hi,

Im using the design view to build a query, and what I need to extract
is all the fields which value is all digits. I've found the suggestion
on this group which says it should be formed like this:

Not Like "*[!0-9]*"

But that doesn't select anything. Any other ideas?

cheers, Morris
 
J

John Spencer

You can try using the VBA IsNumeric function.

Field:NumbersOnly: IsNumeric(YourField)
Criteria: True

What you posted should work unless your field contains leading or trailing
spaces or other non-visible characters.
If you want you can try some variations.
Not Like "*[A-Z]*" Returns all records that don't have an alphabet
character in them.

Also, if you are not using an MDB, but a project (ADB) then the wild card
characters should be "%" instead of "*". Try replacing the "*" with "%" in
the original expression you posted and see if that works for you.

Not Like "%[!0-9]%"
 
R

RoyVidar

John Spencer said:
You can try using the VBA IsNumeric function.

Field:NumbersOnly: IsNumeric(YourField)
Criteria: True

What you posted should work unless your field contains leading or
trailing spaces or other non-visible characters.
If you want you can try some variations.
Not Like "*[A-Z]*" Returns all records that don't have an
alphabet character in them.

Also, if you are not using an MDB, but a project (ADB) then the wild
card characters should be "%" instead of "*". Try replacing the "*"
with "%" in the original expression you posted and see if that works
for you.

Not Like "%[!0-9]%"

Morris said:
Hi,

Im using the design view to build a query, and what I need to
extract
is all the fields which value is all digits. I've found the
suggestion
on this group which says it should be formed like this:

Not Like "*[!0-9]*"

But that doesn't select anything. Any other ideas?

cheers, Morris
Click to expand...
Click to expand...

I'd be a bit careful with the IsNumeric function, because in some
positions, the letters D and E are interpreted as scientific
notation.

In the immediate pane (ctrl+g) try

s = "12345678E1"
? IsNumeric(s) ' returns True
 
F

fredg

Hi,

Im using the design view to build a query, and what I need to extract
is all the fields which value is all digits. I've found the suggestion
on this group which says it should be formed like this:

Not Like "*[!0-9]*"

But that doesn't select anything. Any other ideas?

cheers, Morris
Click to expand...

This seems to work OK for me.

exp: Len(Val([FieldName])) = Len([FieldName])
 
Top