Solved

REgular Expressions in MS Access

Posted on 2007-03-25
5
10,953 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 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

762 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