how do i display records not containing numbers

[Guest]

i have a text field that is for codes that are comprised of surname and a
date of birth (eg. MILNEP020872)

I want to display any fields not containing numbers (eg just MILNE)

Any ideas ?

 

[Joseph Meehan]

i have a text field that is for codes that are comprised of surname
and a date of birth (eg. MILNEP020872)

I want to display any fields not containing numbers (eg just MILNE)

Any ideas ?

You can use a query with a criteria like:

Not Like "*1*" And Not Like "*2*" And Not Like "*3*" ... And Not Like
"*0*"

 

[Graham R Seach]

Philip,

There are several ways to do it; this is just one:

1. Add the code on the following page to a standard module.
http://www.pacificdb.com.au/MVP/Code/StripChars.htm

2. Set the text box's ControlSource property as follows:
=IIf(Len(StripEx([Fieldname],32))>0,[Fieldname],"no good, it's got
numbers!")

Naturally, change the fieldname and the message, as required.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Andi Mayer]

i have a text field that is for codes that are comprised of surname and a
date of birth (eg. MILNEP020872)

I want to display any fields not containing numbers (eg just MILNE)

Any ideas ?

look at the isnumeric() function

 

[Graham R Seach]

Andi,

Unfortunately, the IsNumeric() function can't always be relied upon:
?IsNumeric("1E10")

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Andi Mayer]

Andi,

Unfortunately, the IsNumeric() function can't always be relied upon:
?IsNumeric("1E10")

you are right, I forget every time that I have my ownfunction for
that:

Public Function isNumerical(ByVal theNumber As Variant) As Boolean
Dim I, Nr As Long
If IsNull(theNumber) Then Exit Function
For I = 1 To Len(theNumber)
Nr = Asc(Mid(theNumber, I, 1))
If (Nr < 48 Or Nr > 57) Then Exit Function
Next I
isNumerical = True
End Function

 

[Guest]

Thanks for all your answers.

I went for Jospehs method and this seemed the simplest and gave me the
answer i was looking for.