Query: No Numerical Characters?

[Mark Kolber]

I have a table in a database that contains a text field. The text
field contains identifiers that consist of 7 alphanumeric characters,
such as GORKY, 99SST, and PT10A.

I am trying to run a query that will only display those identifiers
that have no numerical characters, so that, from the examples above,
only GORKY will show.

I apologize is this is an often-repeted request. I searched Google and
Google Groups, but couldn't seem to come up with a search term narrow
enough to find this.

Thanks.

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"

[bzamfir]

Hi,

Try the following (tested in Acc2000):

Select * from yourtable where YourTextField Like
"[a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z]"

Regards,
Bogdan

----------------------------------------------
Freelance programmer

"Mark Kolber" <ma************ *****@yahoo.com > wrote in message
news:gn******** *************** *********@4ax.c om...

I have a table in a database that contains a text field. The text
field contains identifiers that consist of 7 alphanumeric characters,
such as GORKY, 99SST, and PT10A.

I am trying to run a query that will only display those identifiers
that have no numerical characters, so that, from the examples above,
only GORKY will show.

I apologize is this is an often-repeted request. I searched Google and
Google Groups, but couldn't seem to come up with a search term narrow
enough to find this.

Thanks.

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"

[Tom]

Bogdan,

Please explain what the vertical bar dies.

Thanks!

Tom
"bzamfir" <bz*****@despam med.com> wrote in message
news:bo******** *****@ID-189962.news.uni-berlin.de...

Hi,

Try the following (tested in Acc2000):

Select * from yourtable where YourTextField Like
"[a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z]"

Regards,
Bogdan

----------------------------------------------
Freelance programmer

"Mark Kolber" <ma************ *****@yahoo.com > wrote in message
news:gn******** *************** *********@4ax.c om...

I have a table in a database that contains a text field. The text
field contains identifiers that consist of 7 alphanumeric characters,
such as GORKY, 99SST, and PT10A.

I am trying to run a query that will only display those identifiers
that have no numerical characters, so that, from the examples above,
only GORKY will show.

I apologize is this is an often-repeted request. I searched Google and
Google Groups, but couldn't seem to come up with a search term narrow
enough to find this.

Thanks.

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"

[Tom Wickerath]

Hi Bogdan,

Although your query works with the limited test data that Mark provided, it will only
identify those records that are all text with five characters. It doesn't work with less
than or greater than 5 characters, unless a number is present in positions # 1-5.

I recommend instead that each character be inspected to determine if the Identifier should
be included in the recordset. The example shown below uses a table that is named
"tblIdentifiers ". This table has two fields: ID (autonumber) and Identifier (Text).

1) Create a new module. Paste the following function into this module. Watch for word
wrap, especially in the comment present in the code:

Option Compare Database
Option Explicit

Function ContainsANumber (MyStr As Variant) As Integer
Dim i As Integer
Dim intAsc As Integer

If Not IsNull(MyStr) Then
For i = 1 To Len(MyStr)
intAsc = Asc(MyStr)
If intAsc >= 48 Then '>= "0"
If intAsc <= 57 Then '<= "9" If both conditions pass, then we found a number so
we can stop looking
ContainsANumber = 1
Exit Function
End If
End If
MyStr = Right(MyStr, Len(MyStr) - 1)
'Debug.Print MyStr
Next i

End If

End Function

2) Create a query by pasting this SQL clause:

SELECT tblItentifiers. ID, tblItentifiers. Identifier, ContainsANumber ([Identifier])
AS [Includes numbers in Identifier?]
FROM tblItentifiers;

Save the query and name it "qryDetermineIf IdentifierConta insANumber" (without the quotes,
of course).

3) Create another query that uses qryDetermineIfI dentifierContai nsANumber as its source of
data:

SELECT qryDetermineIfI dentifierContai nsANumber.ID,
qryDetermineIfI dentifierContai nsANumber.Ident ifier
FROM qryDetermineIfI dentifierContai nsANumber
WHERE
(((qryDetermine IfIdentifierCon tainsANumber.[Includes numbers in Identifier?])=0));
The first query simply determines if the Identifier includes a number and shows this data
in a calculated field. The second query includes a criteria = 0 (ie. no numbers in
Identifier). This method works with any length of text.

Tom

*************** *************** *************** *************** *************

"bzamfir" <bz*****@despam med.com> wrote in message
news:bo******** *****@ID-189962.news.uni-berlin.de...

Hi,

Try the following (tested in Acc2000):

Select * from yourtable where YourTextField Like
"[a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z]"

Regards,
Bogdan

----------------------------------------------
Freelance programmer

*************** *************** *************** *************** *************

"Mark Kolber" <ma************ *****@yahoo.com > wrote in message
news:gn******** *************** *********@4ax.c om...

I have a table in a database that contains a text field. The text
field contains identifiers that consist of 7 alphanumeric characters,
such as GORKY, 99SST, and PT10A.

I am trying to run a query that will only display those identifiers
that have no numerical characters, so that, from the examples above,
only GORKY will show.

I apologize is this is an often-repeted request. I searched Google and
Google Groups, but couldn't seem to come up with a search term narrow
enough to find this.

Thanks.

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"

[Herbert Chan]

How about simply:

Select * from yourtable where YourTextField Not Like '*[1-9]*'

Herbert

"bzamfir" <bz*****@despam med.com> ¦b¶l¥ó
news:bo******** *****@ID-189962.news.uni-berlin.de ¤¤¼¶¼g...

Hi,

Try the following (tested in Acc2000):

Select * from yourtable where YourTextField Like
"[a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z]"

Regards,
Bogdan

----------------------------------------------
Freelance programmer

"Mark Kolber" <ma************ *****@yahoo.com > wrote in message
news:gn******** *************** *********@4ax.c om...

I have a table in a database that contains a text field. The text
field contains identifiers that consist of 7 alphanumeric characters,
such as GORKY, 99SST, and PT10A.

I am trying to run a query that will only display those identifiers
that have no numerical characters, so that, from the examples above,
only GORKY will show.

I apologize is this is an often-repeted request. I searched Google and
Google Groups, but couldn't seem to come up with a search term narrow
enough to find this.

Thanks.

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"

[Tom Wickerath]

Hi Herbert,

Your solution seems to be the best. It's fairly simple and it works!

Thanks
Tom
_______________ _______________ _______________

"Herbert Chan" <he*****@chan.c om> wrote in message news:bo******** *@rain.i-cable.com...

How about simply:

Select * from yourtable where YourTextField Not Like '*[1-9]*'

Herbert

[bzamfir]

Hi,

Please explain what the vertical bar dies.
Sorry, you can forgot about them. Sometimes I simple forget Access is case
independent. It was intended to pick small letters and uppercases.
And you need 7 char fields, while I made for 5 (I didn't read your post very
carefully, just saw the examples, where was only five chars)

Look at the solution from Herbert, it is better.

Regards,
Bogdan

Thanks!

Tom
"bzamfir" <bz*****@despam med.com> wrote in message
news:bo******** *****@ID-189962.news.uni-berlin.de...

Hi,

Try the following (tested in Acc2000):

Select * from yourtable where YourTextField Like
"[a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z][a-z|A-z]"

Regards,
Bogdan

----------------------------------------------
Freelance programmer

"Mark Kolber" <ma************ *****@yahoo.com > wrote in message
news:gn******** *************** *********@4ax.c om...

I have a table in a database that contains a text field. The text
field contains identifiers that consist of 7 alphanumeric characters,
such as GORKY, 99SST, and PT10A.

I am trying to run a query that will only display those identifiers
that have no numerical characters, so that, from the examples above,
only GORKY will show.

I apologize is this is an often-repeted request. I searched Google and
Google Groups, but couldn't seem to come up with a search term narrow
enough to find this.

Thanks.

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"

[Herbert Chan]

No problem!

I just started out on Access 2 months ago, and so I'm only capable of simple
stuff:-)

Happy using newsgroups! I've got a lot of help from here as well :-) and of
course also from groups.google.c om. it's a real big time saver.

herbert

"Tom Wickerath" <AO************ ***********@com cast.net> ¦b¶l¥ó
news:-6************** ******@comcast. com ¤¤¼¶¼g...

Hi Herbert,

Your solution seems to be the best. It's fairly simple and it works!

Thanks
Tom
_______________ _______________ _______________

"Herbert Chan" <he*****@chan.c om> wrote in message news:bo******** *@rain.i-cable.com...
How about simply:

Select * from yourtable where YourTextField Not Like '*[1-9]*'

Herbert

[Mark Kolber]

On Sun, 9 Nov 2003 10:40:24 +0800, "Herbert Chan" <he*****@chan.c om>
wrote:

Select * from yourtable where YourTextField Not Like '*[1-9]*'

I just =knew= it had to be simple. I knew about the wildcards and the
brackets, but couldn't figure out how to put them together properly.

Thank you Herbert and all of the others who replied!

Mark Kolber
Denver, Colorado
=============== ========
email? Remove ".no.spam"