1

I've a text box in an Access Db that the user should enter an email address into. This is done by copying from Outlook 2010. Unfortunately, that copy also brings the Name and angular brackets, Fred Bloggs <fred@bloggs.co.uk>

I was hoping to use this regex

.pattern = "[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}"

to identify the email address.

How would I strip everything else and throw an error if no valid email address is entered?

Community
  • 1
  • 1
BOOMERANG
  • 11
  • 1

2 Answers2

0

Microsoft Access VBA does not have a built-in Regex engine. You can, however, add a reference to the VBScript Regular Expression Library and use that one.

Here's a related question that explains how to do that:

Community
  • 1
  • 1
Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • 1
    Late binding is superior to creating a reference. – David-W-Fenton Apr 29 '11 at 02:37
  • @David: Well, it depends: For development, early binding helps a lot (IntelliSense, etc.), especially for not-so-experienced developers that don't know the library's object model by heart. Of course, for deployment, switching to late binding saves a lot of trouble. – Heinzi Apr 29 '11 at 05:34
  • I'm oriented towards making things easy and reliable for my clients. Sometimes that means I as the developer have to work harder. – David-W-Fenton Apr 30 '11 at 19:01
  • @David: Well, spotting errors at *compile time* also increases the reliability of the code. ;-) But yes, in general I agree with you. – Heinzi May 01 '11 at 07:31
0

If the email is always enclosed in the final <> you could

Public Function fmt(email As String) As String
pos = InStrRev(email, "<")
If (pos > 0) Then
   email = Mid$(email, 1 + pos, 1 + Len(email) - pos)
   email = Left$(email, Len(email) - 1)
End If
fmt = email
End Function

or

replace(mid(email,instrrev(email,"<")+1,len(email)),">","")

Edit;

For a regexp check add a reference to the "Microsoft VBScript Regular Expressions library" (tools > references) and;

Public Function fmt(email As String) As String
pos = InStrRev(email, "<")
If (pos > 0) Then
   email = Mid$(email, 1 + pos, 1 + Len(email) - pos)
   email = Left$(email, Len(email) - 1)
End If
fmt = email
With New RegExp
    .Global = True
    .IgnoreCase = True
    .MultiLine = True
    .Pattern = "^\S+@\S+\.\S+$"
    If Not .Test(fmt) Then fmt = ""
End With
End Function

This returns a valid email address, or "" if its not valid.

I dropped your RE; reasoning: Using a regular expression to validate an email address

Community
  • 1
  • 1
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Thanks for replying! Well that's the thing, pasted directly in from Outlook it'll be in `<>`, but sometimes users will try to enter nothing at all, and sometimes it'll be entered correctly. I'm looking for a sub/function I think that will cover all bases. But maybe your solution can be used and I just don't know how to implement it? – BOOMERANG Apr 28 '11 at 15:45
  • 1
    It would be best to use late binding for the RegEx library, don't you think? – David-W-Fenton Apr 29 '11 at 02:36