Solved

REgular Expressions in MS Access

Posted on 2007-03-25
5
10,938 Views
Last Modified: 2008-01-09
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
Else
MsgBox "invalid", vbOKOnly
End If


thanks
Sean
0
Comment
Question by:SeanNij
  • 3
5 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 18787668
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

/gustav
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18788531
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).
0
 
LVL 44

Assisted Solution

by:Leigh Purvis
Leigh Purvis earned 250 total points
ID: 18788542
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?)
0
 
LVL 58

Expert Comment

by:harfang
ID: 18790188
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.

Cheers!
(°v°)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18791069
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
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question