How to find records that only contains digits?

  • Thread starter Thread starter Morris
  • Start date Start date
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
 
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]%"
 
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
 
John said:
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]%"
Click to expand...

The same applies when using an MDB in ANSI-92 query mode (see
http://office.microsoft.com/en-us/assistance/HP030704831033.aspx), for
example when using an ADO connection.

The following should be query mode neutral:

WHERE (DT1.word NOT LIKE '%[!0-9]%' AND DT1.word NOT LIKE '*[!0-9]*')

It is particularly important to be 'wildcard-neutral' in SQL code
stored in database-level objects such as a stored query/VIEW,
especially so in validation rules and CHECK constraints, where
switching from, say, DAO to ADO could circumvent engine-level data
integrity.

Jamie.

--
 
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])
 
Back
Top