Data validation of a field in an existing table

[J_Orrell]

Guys you usually come up trumps and save my bacon so I'm hoping you will again.

I'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:

AAnnnnnnA

...where A is any letter A-Z and n is a digit 0-9

Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?

Thanks.

 

[KenHigg]

A function seems like a pretty good option to me...

 

[jdraw]

I agree with Ken. You could also parse the string and check for IsNumeric and Not IsNumeric for each position A or n accordingly.

Good luck.

 

[arnelgp]

Function RegExpr( _
  StringToCheck As Variant, _
  PatternToUse As String, _
  Optional CaseSensitive As Boolean = True) As String

    Dim re As New regexp
    Dim rslt As Variant
    
    re.Pattern = PatternToUse
    re.Global = False
    re.IgnoreCase = Not CaseSensitive
    Dim m
    For Each m In re.Execute(StringToCheck)
        rslt = m.Value
    Next
    
    If IsNull(rslt) Then
        RegExpr = ""
    ElseIf Len(StringToCheck) <> Len(rslt) Then
        RegExpr = ""
    Else
        RegExpr = rslt
    End If
  
End Function

make reference to Microsoft VBScript Regular Expression X.X
to validate:

If RegExpr("AA99AA","[A-Z][A-Z][0-9]{6}[A-Z][A-Z]") = "AA99AA" THEN
'they match
else
'did not match
End if

 

[J_Orrell]

Thanks. Custom function it is then.

 

[KenHigg]

Let us know if it will rip through 400k records in a resonable amount of time -

 

[KenHigg]

Function RegExpr( _
  StringToCheck As Variant, _
  PatternToUse As String, _
  Optional CaseSensitive As Boolean = True) As String

    Dim re As New regexp
    Dim rslt As Variant
    
    re.Pattern = PatternToUse
    re.Global = False
    re.IgnoreCase = Not CaseSensitive
    Dim m
    For Each m In re.Execute(StringToCheck)
        rslt = m.Value
    Next
    
    If IsNull(rslt) Then
        RegExpr = ""
    ElseIf Len(StringToCheck) <> Len(rslt) Then
        RegExpr = ""
    Else
        RegExpr = rslt
    End If
  
End Function

make reference to Microsoft VBScript Regular Expression X.X
to validate:

If RegExpr("AA99AA","[A-Z][A-Z][0-9]{6}[A-Z][A-Z]") = "AA99AA" THEN
'they match
else
'did not match
End if

Cool - I'll have to copy this to my snippets

 

[jdraw]

My guess is the regex will be faster.

More info here if you are unfamiliar with RegEx