Find Numbers in Text Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
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...
 
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]
 
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]
 
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...
 
Back
Top