Select non numeric records

[news.microsoft.com]

I have a table of data from which I want to extra only the Accounts that are
letters or a combination of letters and numbers.

The field is a text field and includes, for eg

1000
1456
ABC1
AB

I want to select only the records ABC1 and AB
How can I do this?

Thanks

 

[news.microsoft.com]

I have tried a simple >="A" which seems to work except it doesn't catch the
records that begin with a number
eg 0AB1

How can I get around this?

 

[Tom Ellison]

IsNumeric([YourColumnName]) <> 0

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[news.microsoft.com]

Thanks - works a treat

IsNumeric([YourColumnName]) <> 0

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a table of data from which I want to extra only the Accounts that are
letters or a combination of letters and numbers.

The field is a text field and includes, for eg

1000
1456
ABC1
AB

I want to select only the records ABC1 and AB
How can I do this?

Thanks

 

[John Spencer (MVP)]

Strictly SQL would probably be something like the following.

LIKE "*![0123456789]*"

Thanks - works a treat

IsNumeric([YourColumnName]) <> 0

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a table of data from which I want to extra only the Accounts that are
letters or a combination of letters and numbers.

The field is a text field and includes, for eg

1000
1456
ABC1
AB

I want to select only the records ABC1 and AB
How can I do this?

Thanks

 

[John Spencer (MVP)]

Whoops, that should have had the ! inside the brackets

LIKE "*[!0-9]*"

OR
LIKE "*[!0123456789]*"

Thanks - works a treat

IsNumeric([YourColumnName]) <> 0

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

I have a table of data from which I want to extra only the Accounts that are
letters or a combination of letters and numbers.

The field is a text field and includes, for eg

1000
1456
ABC1
AB

I want to select only the records ABC1 and AB
How can I do this?

Thanks

 

[Newbie]

Thanks for this
Could you explain how it works please ?

Whoops, that should have had the ! inside the brackets

LIKE "*[!0-9]*"

OR
LIKE "*[!0123456789]*"

Thanks - works a treat

IsNumeric([YourColumnName]) <> 0

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 27 Sep 2004 10:27:25 +0100, "news.microsoft.com"

I have a table of data from which I want to extra only the Accounts
that are
letters or a combination of letters and numbers.

The field is a text field and includes, for eg

1000
1456
ABC1
AB

I want to select only the records ABC1 and AB
How can I do this?

Thanks

 

[John Spencer (MVP)]

Try the help on wildcards.

But basically.

* = any number of characters including zero characters
! inside the bracket means NOT any of the following characters in the bracket

SO

Any number of characters followed by something that is Not a number character
(0-9) followed by any number of characters.

So Any letter in any string meets that criteria. Also, a space or any other
non-number character such as "%" would also meet the criteria.

Thanks for this
Could you explain how it works please ?

Whoops, that should have had the ! inside the brackets

LIKE "*[!0-9]*"

OR
LIKE "*[!0123456789]*"

Thanks - works a treat
IsNumeric([YourColumnName]) <> 0

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 27 Sep 2004 10:27:25 +0100, "news.microsoft.com"

I have a table of data from which I want to extra only the Accounts
that
are
letters or a combination of letters and numbers.

The field is a text field and includes, for eg

1000
1456
ABC1
AB

I want to select only the records ABC1 and AB
How can I do this?

Thanks