Find Numbers in Text Field

S

Shel

Does anyone know how I can get a query to find the records where the
"Address" field begins with a number even though the field's data type is
text?
 
S

Shel

I simply made a field that picks up only the 1st left character from the field
then I added the not "#" criteria. See example below.

Expr1: Left([address1],1)
Not "1" And Not "2" And Not "3" And Not "4" And Not "5" And Not "6" And Not
"7" And Not "8" And Not "9" And Not "0"

This may be a tad crude but it works.
 
C

Chaim

Try using using Val(), something like:
select field1, field2, etc.
from MyTable
where Val(MyTable.address_field) <> 0

In the QEB (working backwards from the SQL), I see:
Field: Val(MyTable.address_field)
Criteria: <> 0

Shel said:
I simply made a field that picks up only the 1st left character from the field
then I added the not "#" criteria. See example below.

Expr1: Left([address1],1)
Not "1" And Not "2" And Not "3" And Not "4" And Not "5" And Not "6" And Not
"7" And Not "8" And Not "9" And Not "0"

This may be a tad crude but it works.



Shel said:
Does anyone know how I can get a query to find the records where the
"Address" field begins with a number even though the field's data type is
text?
Click to expand...
Click to expand...
 
J

John Vinson

Does anyone know how I can get a query to find the records where the
"Address" field begins with a number even though the field's data type is
text?
Click to expand...

A query criterion of

LIKE "[0-9]*"

will do the trick - returning records where the first character in the
field is a digit.

John W. Vinson[MVP]
 
J

John Vinson

I simply made a field that picks up only the 1st left character from the field
then I added the not "#" criteria. See example below.

Expr1: Left([address1],1)
Not "1" And Not "2" And Not "3" And Not "4" And Not "5" And Not "6" And Not
"7" And Not "8" And Not "9" And Not "0"

This may be a tad crude but it works.
Click to expand...

As noted elsethread - it's not that hard. <g>

LIKE "[0-9]*"

John W. Vinson[MVP]
 
S

Shel

thanks for the simpler solution!

John Vinson said:
Does anyone know how I can get a query to find the records where the
"Address" field begins with a number even though the field's data type is
text?
Click to expand...

A query criterion of

LIKE "[0-9]*"

will do the trick - returning records where the first character in the
field is a digit.

John W. Vinson[MVP]
Click to expand...
 
Top