Getting string to be all Alphabet in text box or non-numeric

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

Guest

Could someone help me with my problem? It seems that I can't get any of my
code to run properly in the module that I created withthe help of MS help the
code is as follows:
Option Compare Database

'**********************************
'Declarations section of the module
'**********************************
Option Explicit

'===============================================================
' The following function is named IsAlpha(). It accepts a string
' argument and returns either True (-1) or False (0).
'===============================================================

Function IsAlpha(MyString As String) As Integer
Dim LoopVar As Integer
Dim SingleChar As String

LoopVar = 1

If IsNull(MyString) Then
IsAlpha = False
Exit Function
End If

For LoopVar = 1 To Len(MyString)
SingleChar = UCase(Mid$(MyString, LoopVar, 1))
If SingleChar < "A" Or SingleChar > "Z" Then
IsAlpha = False
Exit Function
End If
Next LoopVar

IsAlpha = True

End Function
Can't seem to find out what is wrong? It give me an invalid value for the
text box? Help Please.
 
Stanley,

It's not very clear what you mean by "It gives me an invalid value for the
text box". Any chance you mean you see an empty textbox which is the
function argument, yet you get a True value while you expected a False? If
that's the case, then most likely it's because the textbox contains a zero
length string ("") which you don't see, but:
While you have accounted for a null string in your code, you have not for a
zero length string (""). In the latter case, the check for null will fail (a
zero length string is not null) and the code execution will proceed to the
For loop, which will not be executed because Len("") = 0, which is less than
1. As a result, the function will return a True value, which is wrong. To
deal with this, change the first If to:

If IsNull(MyString) Or MyString = "" Then


Also, you are assigning a True/False (boolean) value to an integer variable;
it works (because True is actually -1 and False is 0), but it's tricky.
Change to:

Function IsAlpha(MyString As String) As Boolean

HTH,
Nikos
 
hey I tried the new changes but now it gives me a value entered does not meet
validation error. When I tried to enter it as part of the validation rule
for that control. Is there an other way that I'm overlooking? Thanks for
any help
 
Stan,

How are you using the function? If you want to use it for validating the
input in a control, then put the following expression in the control's
Validation Rule property:

IsAlpha([ControlName]) = True

where ControlName is the actual name of the control.

HTH,
Nikos
 
First, this function will fail before you even start if you try to pass it a
NULL value. You have specified in the declaration line that MyString IS a
string. It will also fail if you try to pass it a number, a date, a boolean, ...

Second, why aren't you defining the returned value as a BOOLEAN instead of
Integer.

Also, you don't need to initialize LoopVar to 1, that is a wasted step since the
For statement will automatically set LoopVar to 1.

Since the character comparison is not case sensitive, you shouldn't need to
force the case to UCASE.


Function IsAlpha(MyString) as Boolean
Dim LoopVar As Integer
Dim SingleChar As String

'Immediately handle nulls, zero-length strings,
'and strings that consist of only spaces
If Len(Trim(MyString & VbNullstring)) > 0 Then
IsAlpha = False
Exit Function
End If

For LoopVar = 1 To Len(MyString)
SingleChar = Mid$(MyString, LoopVar, 1))
If SingleChar < "A" Or SingleChar > "Z" Then
IsAlpha = False
Exit Function
End If
Next LoopVar

IsAlpha = True

End Function
 
So how can I get it to accept special characters like "(" or ")" or "-" along
with the just Alphabet characters
 
Thank you for your help it was very helpful

Nikos Yannacopoulos said:
Stan,

How are you using the function? If you want to use it for validating the
input in a control, then put the following expression in the control's
Validation Rule property:

IsAlpha([ControlName]) = True

where ControlName is the actual name of the control.

HTH,
Nikos

Stan H. said:
hey I tried the new changes but now it gives me a value entered does not meet
validation error. When I tried to enter it as part of the validation rule
for that control. Is there an other way that I'm overlooking? Thanks for
any help
Click to expand...
Click to expand...
 
I see you've opened another thread on this and gotten answers going there.

One way would be to build a string of acceptable values and use the instr function

strGoodValues = "[]()abcdefghijklmnopqrstuvwxyz"

If Instr(1,StrGoodValues,SingleChar,vbTextCompare) = 0 Then
...

Or you could use the like operator

IF UCase(SingleChar) Like "[() A-Z]" = False Then
...

or even the following which should do it all in one line

If UCase(MyString) Like "*[!() A-Z]*" = True Then
...

Function isAlpha(MyString) as Boolean

If Len(MyString & "") = 0 Then
IsAlpha = False
Else
IsAlpha = Not(UCase(MyString) Like "*[!() A-Z]*")
End If

End function

The like statement
UCase(MyString) Like "*[!() A-Z]*"
basically says does MyString contain any character that is NOT (the !) in the
set of characters (,), <Space>, or A to Z.
 
Thanks John,
John Nurick from MVP helped me a validation function from
http://www.mvps.org/access
which solved almost all of my problems for ckecking an expresion but thanks
for helping me look at it from another angle
CHEERS
John Spencer (MVP) said:
I see you've opened another thread on this and gotten answers going there.

One way would be to build a string of acceptable values and use the instr function

strGoodValues = "[]()abcdefghijklmnopqrstuvwxyz"

If Instr(1,StrGoodValues,SingleChar,vbTextCompare) = 0 Then
...

Or you could use the like operator

IF UCase(SingleChar) Like "[() A-Z]" = False Then
...

or even the following which should do it all in one line

If UCase(MyString) Like "*[!() A-Z]*" = True Then
...

Function isAlpha(MyString) as Boolean

If Len(MyString & "") = 0 Then
IsAlpha = False
Else
IsAlpha = Not(UCase(MyString) Like "*[!() A-Z]*")
End If

End function

The like statement
UCase(MyString) Like "*[!() A-Z]*"
basically says does MyString contain any character that is NOT (the !) in the
set of characters (,), <Space>, or A to Z.

So how can I get it to accept special characters like "(" or ")" or "-" along
with the just Alphabet characters
Click to expand...
Click to expand...
 
Back
Top