Query to find non numeric characters

[Jerry]

I'm trying to find any non numeric characters in a field defined as
text with a length of 20. The values in this field should be 6 characters
with numeric data in the records I'm looking for (e.g. - "123456").

There are some rows that have values such as "123456-", however
my query isn't returning them. Here is the syntax I'm using:

SELECT Trim([other_id]) AS Expr1
FROM ids
WHERE (((Trim([other_id])) Like '[000000-999999]'))
;

Any help would be greatly appreciated.

Thanks,

Jerry

 

[John Spencer (MVP)]

How about

Where [Other_ID] LIKE "*[!0-9]*"

IF I recall my wild cards correctly that should find any id that contains a
character that is not in the range zero to nine.

 

[Marshall Barton]

I'm trying to find any non numeric characters in a field defined as
text with a length of 20. The values in this field should be 6 characters
with numeric data in the records I'm looking for (e.g. - "123456").

There are some rows that have values such as "123456-", however
my query isn't returning them. Here is the syntax I'm using:

SELECT Trim([other_id]) AS Expr1
FROM ids
WHERE (((Trim([other_id])) Like '[000000-999999]'))

The [...] wildcard syntax only matches a single character
that's one of the ones in the brackets.

To find records tha only contain digits:
WHERE NOT ([other_id] Like '*[!0-9]*')

Or, to find the records that contain any non-digit
character:
WHERE [other_id] Like '*[!0-9]*'

 

[Jerry]

Awesome - that worked perfect!

Many Thanks

Jerry