Solved

REgular Expressions in MS Access

Posted on 2007-03-25
5
10,960 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 50

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

737 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