Validating a string format in VBA Access

Question

I would like to write a Boolean function that checks that Medicaid IDs are in the required format.
Namely 2 alpha characters followed by 5 digits followed by 1 alpha character.
If the Medicaid ID is not available then 99999999 should be entered manually into the text box.

So it's either 9999999 or the required Medicaid formatted string that return a value of True.

Samples:

AZ12345Z
NP54321J
EM17345P

So far I have 2 functions working together but I made a mess of the logic!!

Thank you

Public Function isAlpha(cChar As Integer) As Boolean
'returns true if its a alphabetic character
    isAlpha = IIf((cChar >= 65 And cChar <= 90) Or (cChar >= 97 And cChar <= 122), True, False)   
End Function

Public Function CheckMedicaidIDFormat(strMedicaidID As String) As Boolean
    Dim blnResult As Boolean
    If strMedicaidID = "99999999" or If Len(strMedicaidID) = 8 And isAlpha(Left(strMedicaidID, 2)) = True And IsNumeric(Mid(strMedicaidID, 3, 5)) = True And isAlpha(Right(strMedicaidID, 1)) = True Then 

        blnResult = True
    Else
        blnResult = False
    End If
    CheckMecicaidIDFormat = blnResult
End Function

chris neilsen · Accepted Answer · 2014-05-03 10:23:29Z

6

While RegEx is a good general solution for this type of problem, in this case a simple Like comparison will do

Function IsValid(strIn As String) As Boolean
    IsValid = (strIn Like "[A-Z][A-Z]#####[A-Z]") Or strIn = "99999999"
End Function

answered May 3, 2014 at 10:23

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2014-05-03 10:53:13Z

3

Something like this

Sub Test()
MsgBox IsValid("AZ12345Z")
MsgBox IsValid("1AZ12345Z")
End Sub

test function

Function IsValid(strIn As String) As Boolean
If strIn = "99999999" Then
IsValid = True
Else
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "^[A-Z]{2}\d{5}[A-Z]$"
IsValid = .Test(strIn)
End With
End If
End Function

edited May 3, 2014 at 10:53

answered May 3, 2014 at 5:31

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

2 Comments

chris neilsen Over a year ago

The Beginning of string and End of string patterns need to apply to both alternatives. Also, OP want alternative to be 8 x 9's. Maybe ^(?:([A-Z]{2}\d{5}[A-Z])|([9]{8}))$ or (^[A-Z]{2}\d{5}[A-Z]$)|(^[9]{8}$)

brettdj Over a year ago

@chrisneilsen fair points. Ive stripped back out the simple test out rather than add to the regexp.

Dai · Accepted Answer · 2014-05-03 09:59:15Z

0

VBA has support for Regular Expressions using the COM VBScript.RegExp object, like so:

Dim regex As VBScript.RegExp
Set regex = New VBScript.RegExp ' or CreateObject("VBScript.RegExp")
regex.Pattern = "^[A-Za-z][A-Za-z]\d\d\d\d\d[A-Za-z]$"

If medicId = "99999999" Or regex.Test( medicId ) Then
    ' code to handle valid Medicaid ID here
End If

edited May 3, 2014 at 9:59

answered May 3, 2014 at 4:01

Dai

158k31 gold badges314 silver badges438 bronze badges

3 Comments

brettdj Over a year ago

I think OP wants an alpha character at start and end not alphanumeric.

Maria6460 Over a year ago

I've never used VBScript in any of my Access databases. Very interesting solution. Much appreciated!

Dai Over a year ago

@Maria6460 This isn't VBScript. You're still using VBA, but making use of a component originally designed for VBScript (i.e. the regex class), but which is still fully-compatible with VBA.

Jzz · Accepted Answer · 2014-05-03 10:49:22Z

0

Regex is very suitable for this, but if you prefer not to use it (and many people do, for various reasons) it is very possible to do without.

You made a small mistake in the if statement in the CheckMediacaidIDFormat function. 'If' is mentioned twice and you forgot some parentheses. I would formulate like this:

Public Function CheckMedicaidIDFormat(strMedicaidID As String) As Boolean
    Dim blnResult As Boolean
    If strMedicaidID = "99999999" Or _
            (Len(strMedicaidID) = 8 And _
            isAlpha(Left(strMedicaidID, 2)) = True And _
            IsNumeric(Mid(strMedicaidID, 3, 5)) = True And _
            isAlpha(Right(strMedicaidID, 1)) = True) _
            Then

        blnResult = True
    Else
        blnResult = False
    End If
    CheckMecicaidIDFormat = blnResult
End Function

Note that I use line breaks (space followed by underscore in vba) for readability and parentheses around the 'And' conditions. You can do without the line breaks if you wish, but not without the parentheses.

edited May 3, 2014 at 10:49

answered May 3, 2014 at 6:55

Jzz

7394 silver badges8 bronze badges

1 Comment

Maria6460 Over a year ago

Nice work. I think I'll need to use the mid function to check if the second character is alpha as well. Thanks for your time and effort.

Collectives™ on Stack Overflow

Validating a string format in VBA Access

4 Answers 4

Comments

2 Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related