• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11040
  • Last Modified:

REgular Expressions in MS Access

Hi all,

I'm trying to get "Regular Expressions" to work in MS Access. I am relatively new to "RegExp" and have been reading up on it in experts exchange. I've created a module in Access and added the reference to Microsoft VBScript Regualr Expressions 1.0 (i see there is also a 5.5). However, in trying to use it I've created the following code and when i run it i get Compile Error: Expected user defined type, not project. Any ideas?

Set RegularExpressionObject = New RegExp

With RegularExpressionObject
 .Pattern = "\b[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z0-9._%-]{2,4}\b"
 .IgnoreCase = True
 .Global = True
End With

If RegularExpressionObject.Test(EmailAddress.Value) Then
MsgBox "valid", vbOKOnly
MsgBox "invalid", vbOKOnly
End If

  • 3
2 Solutions
Gustav BrockCIOCommented:
You need to dim it as well:

' Need to Set reference to Microsoft VBScript Regular Expressions 5.5 vbscript.dll/3 or set for 5.6
  Dim regEx As Object
  Set regEx = New RegExp

Leigh PurvisDatabase DeveloperCommented:
I'm not here OK? (On an EE break - so I couldn't possibly be here... shhhh ;-)

However - judging from your error message, I'd wager that you've named your application RegExp. (i.e. RegExp.mdb)
Either change it or qualify your reference like:

Set RegularExpressionObject = New VBScript_RegExp_55.RegExp

(I'd tend toward the latter - but you *always* need to be careful when naming objects).
Leigh PurvisDatabase DeveloperCommented:
Oh pants - you're using 1.0 aren't you.
Set RegularExpressionObject = New VBScript_RegExp_10.RegExp

(Oh and as Gustav mentions - you'll have declared your object somewhere first yes?)
I agree with Leigh and believe the error message is correct: the name of your project is probably RegExp. I just wanted to explain how you can change it. Renaming the database will not rename the project (it's set once when you create the database). Instead, select your project in the Project Explorer (Ctrl+R) and change the name, or choose "tools / <your project> properties" and change it there.

Leigh PurvisDatabase DeveloperCommented:
Yep - I did a check before posting but (as I am desperately trying to get away from here but keep being pulled back by old, slow, lingering, existing questions) I only checked the application name and didn't spend the extra 10 seconds to check the VBA project name.  :-(
Appologies - I'm afraid I'm not of much help at the moment as I strive to escape. :-S

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now