在 VBA Access 中验证字符串格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23440049/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Validating a string format in VBA Access
提问by Maria6460
- 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.
- 我想编写一个布尔函数来检查医疗补助 ID 的格式是否符合要求。
- 即 2 个字母字符后跟 5 个数字后跟 1 个字母字符。
- 如果 Medicaid ID 不可用，则应在文本框中手动输入 99999999。
So it's either 9999999 or the required Medicaid formatted string that return a value of True.
因此，它是 9999999 或返回 True 值的必需 Medicaid 格式字符串。
Samples:
样品：
AZ12345Z
NP54321J
EM17345P
AZ12345Z
NP54321J
EM17345P
So far I have 2 functions working together but I made a mess of the logic!!
到目前为止，我有 2 个函数一起工作，但我把逻辑弄得一团糟！！
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
回答by chris neilsen
While RegExis a good general solution for this type of problem, in this case a simple Likecomparison will do
虽然RegEx是此类问题的一个很好的通用解决方案，但在这种情况下，可以进行简单的Like比较
Function IsValid(strIn As String) As Boolean
IsValid = (strIn Like "[A-Z][A-Z]#####[A-Z]") Or strIn = "99999999"
End Function
回答by brettdj
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
回答by Jzz
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:
您在 CheckMediacaidIDFormat 函数的 if 语句中犯了一个小错误。“如果”被提及两次，你忘记了一些括号。我会这样表述：
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.
请注意，我使用换行符（vba 中的空格后跟下划线）以提高可读性，并在“And”条件周围使用括号。如果您愿意，可以不使用换行符，但不能不使用括号。
回答by Dai
VBA has support for Regular Expressions using the COM VBScript.RegExpobject, like so:
VBA 支持使用 COMVBScript.RegExp对象的正则表达式，如下所示：
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
