Hi,
I'm working with regular expressions in VBScript and I'd like to implement them in VBA in a module. However, in the helpfile from VBScript I found:
VBScript Features not in Visual Basic for Applications : among others the function "RegExp" (the use of Regular expressions).
However, on the web, I found that there do is a possibility to apply regular expressions in Access. An example on the folowing site: http://www.timallen.org/blog/article.php?story=msaccess_zipcode
THe example on this site is to select the zip code out of the table "some table" with values like "Philadelphia, PA 19107-1234" or "Lumberton, TX 77657" and thus have it return just the ZIP.
You have to include a new Access module, and put this code in it. Prior to putting the code in, you have to include a reference to "Microsoft VBScript Regular Expressions 5.5" (Tools->References):
Function zipfinder(t As String)
Dim re As New RegExp
' Look for five digits. Optionally, look for a dash and four digits
re.Pattern = "b(d{5}(-d{4})?)b"
' This version finds the first match.
' If you want the last match, set Global to false.
re.Global = False
Dim m
For Each m In re.Execute(t)
zipfinder = m.Value
Next
End Function
Then you can use this as a function in a SELECT statement:
select zipfinder( ZIP_expression)
from some_table;
So I did:
-made a new table "some table with the field 'ZIP_expression' and added the two examples
-made a module with the above code pasted
-made a select query to obtain the result
However, this doesn't work? Does anyone can help me with this? Send me an example on how to use regular expressions in Access?
Thanks a lot!!!
However, I can't implement any example.
I'm working with regular expressions in VBScript and I'd like to implement them in VBA in a module. However, in the helpfile from VBScript I found:
VBScript Features not in Visual Basic for Applications : among others the function "RegExp" (the use of Regular expressions).
However, on the web, I found that there do is a possibility to apply regular expressions in Access. An example on the folowing site: http://www.timallen.org/blog/article.php?story=msaccess_zipcode
THe example on this site is to select the zip code out of the table "some table" with values like "Philadelphia, PA 19107-1234" or "Lumberton, TX 77657" and thus have it return just the ZIP.
You have to include a new Access module, and put this code in it. Prior to putting the code in, you have to include a reference to "Microsoft VBScript Regular Expressions 5.5" (Tools->References):
Function zipfinder(t As String)
Dim re As New RegExp
' Look for five digits. Optionally, look for a dash and four digits
re.Pattern = "b(d{5}(-d{4})?)b"
' This version finds the first match.
' If you want the last match, set Global to false.
re.Global = False
Dim m
For Each m In re.Execute(t)
zipfinder = m.Value
Next
End Function
Then you can use this as a function in a SELECT statement:
select zipfinder( ZIP_expression)
from some_table;
So I did:
-made a new table "some table with the field 'ZIP_expression' and added the two examples
-made a module with the above code pasted
-made a select query to obtain the result
However, this doesn't work? Does anyone can help me with this? Send me an example on how to use regular expressions in Access?
Thanks a lot!!!
However, I can't implement any example.