Find Numbers in Text Field

[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?

 

[Guest]

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.

 

[Guest]

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

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.

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?

 

[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?

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]

 

[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.

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

LIKE "[0-9]*"

John W. Vinson[MVP]

 

[Guest]

thanks for the simpler solution!

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?

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]