Question Keeping date in form after update

[PMENDOZA]

I need to determine if a field contains only alphabetic characters (A-Z or a-z). Is there a function like Isnumeric for Alphabetic characters?
I have a field which can contain only numbers ,or, letters and numbers, but NOT only letters.
Any help would be really appreciated.
Thanks,
Pablo

 

[arnelgp]

if you are using form, you can validate the textbox:

private sub textbox1_beforeupdate()
if isAlphA(me.textbox) then
'ok alpha only
else
'not alpha
end if
end sub


create this function in a module:

public function isAlpha(p as variant) as boolean
dim ln as long
dim i as long
p=p & ""
ln=len(p)
isApha=false
if ln=0 then exit function
isAlpha=true
for i = 1 to ln
if mid(p, i, 1) like "[a-z]"
else
isAlpha=false
exit for
end if
next i
end function

 

[Pat Hartman]

Here is an Is Alphabetic function that I wrote. It has an option that allows numbers as well as letters.

Function IsAlphabetic(char As String, Optional IncludeNumbers As Boolean) As Boolean
    If Len(char) <> 1 Then      'check length
        IsAlphabetic = False
        Exit Function
    End If
    If (Asc(char) >= 65 And Asc(char) <= 90) Or (Asc(char) >= 97 And Asc(char) <= 122) Then
        IsAlphabetic = True
    Else
        If IncludeNumbers = True Then
            If Asc(char) >= 48 And Asc(char) <= 57 Then  '0-9
                IsAlphabetic = True
            Else
                IsAlphabetic = False
            End If
        Else
            IsAlphabetic = False
        End If
    End If
End Function

You can use this concept to write your own Is Numeric function. Remember the VBA IsNumeric() function will say that some things are numeric which you might not like such as

print isnumeric(-23)
True
print isnumeric(2.33)
True
print isnumeric (3d4)
True

 

[PMENDOZA]

Thanks, Appreciate the help.!!

 

[Galaxiom]

Another function uses Regular Expressions. I expect this would be considerably more efficient than testing character by character in VBA.

Public Function AlphaOnly(ByVal SearchTarget As String) As Boolean
 
Dim re As Object
Dim Match As Variant
 
    Set re = CreateObject("vbscript.RegExp")
    re.Pattern = "^[A-Za-z]+$"
    
    For Each Match In re.Execute(SearchTarget)
        AlphaOnly = Len(Match.Value)
    Next
 
End Function

This pattern allows upper and lower case. Include any other character inside the square brackets if you need to allow them.

For example, the following pattern allows numerals and spaces too.

 re.Pattern = "^[A-Za-z0-9 ]+$"

It works like this. The caret means beginning. The dollar means end. A-Z means upper case alpha, a-z means lower case alpha, 0-9 means numerals. A single character is taken literally. Precede any of the metacharacters with backslash if you want to search for them literally. The + means any number of the characters in the brackets.

https://www.regular-expressions.info/refcharacters.html

This is a trivial search for RegEx. Regular Expressions can find complex patterns that are quite impractical to discover any other way so they are really worth learning how to use.

This version of the function uses Late Binding. A faster running alternative would be to set a reference to the Microsoft VBScript Regular Expressions Library, change the Dim re As Object line to:

Dim re As New RegExp

and drop the Set line.