I spend most of my time writing SQL queries for Oracle and I've gotten used to functions like REGEXP_LIKE. Lately, however, I have been training a work-study student how to use MS Access for reporting and no such function seemed to exist. I generally try to shy away from delving into advanced MS Access features, but my works-study needs regular expressions and I want my work-study to learn. So, I scrounged the internet and came up with this.
regexp(<string>, <pattern> [, case sensitive = true])
returns the matched string, null otherwise.
Examples:
regexp("UMS00123456", "UMS[0-9]{8}") would return UMS00123456
regexp("00123456", "UMS[0-9]{8}") would return null
In Access press ALT-F11. Click Insert > Module. Click Tools > References then tick "Microsoft VBScript Regular Expressions 5.5" Then paste the following into the module:
Option Compare Database
Option Explicit
Function regexp( _
StringToCheck As Variant, _
PatternToUse As String, _
Optional CaseSensitive As Boolean = True)
Dim re As New regexp
re.Pattern = PatternToUse
re.Global = False
re.IgnoreCase = Not CaseSensitive
Dim m
For Each m In re.Execute(StringToCheck)
regexp = m.Value
Next
End Function
Enjoy. Let me know if there is a better way.