Regular expressions (1 Viewer)

[Leen]

Hi,

I'm working with regular expressions in VBScript and I'd like to implement them in VBA in a module. However, in the helpfile from VBScript I found:
VBScript Features not in Visual Basic for Applications : among others the function "RegExp" (the use of Regular expressions).

However, on the web, I found that there do is a possibility to apply regular expressions in Access. An example on the folowing site: http://www.timallen.org/blog/article.php?story=msaccess_zipcode

THe example on this site is to select the zip code out of the table "some table" with values like "Philadelphia, PA 19107-1234" or "Lumberton, TX 77657" and thus have it return just the ZIP.

You have to include a new Access module, and put this code in it. Prior to putting the code in, you have to include a reference to "Microsoft VBScript Regular Expressions 5.5" (Tools->References):


Function zipfinder(t As String)
Dim re As New RegExp
' Look for five digits. Optionally, look for a dash and four digits
re.Pattern = "b(d{5}(-d{4})?)b"
' This version finds the first match.
' If you want the last match, set Global to false.
re.Global = False
Dim m
For Each m In re.Execute(t)
zipfinder = m.Value
Next
End Function

Then you can use this as a function in a SELECT statement:

select zipfinder( ZIP_expression)
from some_table;

So I did:
-made a new table "some table with the field 'ZIP_expression' and added the two examples
-made a module with the above code pasted
-made a select query to obtain the result

However, this doesn't work? Does anyone can help me with this? Send me an example on how to use regular expressions in Access?

Thanks a lot!!!












However, I can't implement any example.

 

[RoyVidar]

Did you set the reference, as noted? You will need that, if you're doing early binding.

If the reference isn't there, you'll need to register it.

What is the error message(s)?

Did you search these fora for regular expressions? There are some posts where it is mentioned, here is one recent one, demonstrating usage of late binding (note the declaration and instantiation) http://www.access-programmers.co.uk/forums/showthread.php?t=119899

John Nurick also uses late binding http://www.mvps.org/access/modules/mdl0063.htm

 

[Leen]

Hi,

Thanks for the tips. In Access 1997 I could'nt set the references. But now, after installing Access 2000, I vinked both the reference "Microsoft VBScript Regular Expressions 1.0 and 5.5" and it worked.

However, In my company, everybody still works still with Access 1997 because of the format of old data, but is there a possibility to apply regular expressions in that version also (you wrote about somehow to register it)?

Thanks a lot!

 

[RoyVidar]

With a recent version of internet explorer (5.0 and later?), you should be able to use late binding, as is demonstrated in the threads referenced.

But I think you can also add the references programatically in 97 - now, the following is just typed down from the top of my head, but it utilizes the .addfromfile method of the references collection, which is supposed to be able to add it

Sub SetRef(YourFile as String)

	dim ref                    as reference

	on error resume next
	set ref = references.addromfile(YourFile)
	if err.number = 0 then
		msgbox  "did it"
	else
		msgbox "ouch"
	end if
	on error goto 0

end function

Try calling like this

SetRef "c:\<path to>\vbscript.dll\3"

for 5.5, or

SetRef "c:\<path to>\vbscript.dll\2"

for 1.0. "<path to>" might be windows\system

 

[Leen]

Hi,

Thanks again!
Actually, I'm just "new in the world of programming" so I tried as you said to make a late binding and it works!!!

Actually, your second option, to use the addfromfile.method, I don't understand: I searched using Google desktop the vbscript.dll file but it is not apparently not present on my computer. Is that normal? I mean, somewhere it should be installed as I can use vbscript no? Or does it works via the internet (I'm not at all a big expert ).

And if you have maybe bit of time, I tried the folowing code to be able to add all records who do not start with NH (Uppercase) into a next field (to try the use of RegEx out on a table). However, the code bugs each time: bug on the line:strColumn2 = rst.Fields(1) : "invalid use of null"?

(My table name is Names, my first field = Names (text), my second field = RealNames (text). For the moment, in the second field, no data are present yet as I want them to come from the code (see attach).

You have any idea where I should look to solve this?
Thanks!!!

 

[RoyVidar]

"new in the world of programming" AND doing regexp, now, ain't that just something

I had problems finding that stuff on vbscript/97 too. The usage of the addfromfile method, and using \2 or \3 for different versions, is something I found years ago somewhere on net, and just remembered enough to post what I thought was working. On a 97 computer I checked, vbscript.dll is in c:\windows\system. Since your late bound experiments work, it means you do have the file somewhere.

The pattern - it tests for uppercase N somewhere within the string. * means 0 or more of the previous expression. I think I'd try something like

"^NH+"

check out http://regexlib.com/CheatSheet.aspx when you're building patterns.

Null is traditionally a marker to denote missing values. In VBA, only one datatype, can be assigned Null, that is Variant, so, what you're doing, is trying to assign Null to a datatype which cannot be assigned Null (String), which makes it barf.

There are many ways of handling this. A VB(A) way, is using the IsNull function, but Access also has a NZ function, where both can be used to either not assign anything, or assign alternative values. Check them out in the help file - some even likes this method

MyString = rs.fields("MyField").value & vbnullstring

which, if the field is Null, will concatenate it with an empty string

Looking at your logic, you don't need to assign rs.fields(1).value to strColumn2. You can remove that line.

BTW - the recommendation or consensus, if you like, is to not rely on ordinal position, but use the field names, i e some variation over the theme

rs.fields("MyField").value

In stead of

strColumn2 = Trim(prohibNH(Trim(strColumn1)))

try

strColumn2 = Trim(prohibNH(Trim(strColumn1)))
if len(strColumn2) then
    rs.edit
    rs.fields("RealNames").value = strColumn2
    rs.update
end if

(I've just typed this, though, not tested - you'd need to change the OpenTypeEnum, or just remove the dbopensnapshot constant)

Please also post code within the fora. It makes it much easier

Check out the vB code link on the bottom for tags to make code indented (the # button in the reply pane)

 

[Leen]

Hi,

Thanks really a lot for all help. You helped me a real step further in understanding. I think I'm very near the final answer, but for some reason or another, when I run the sub procedure, Access bugs and is not responding each time (so that I have to close the program down without seeing the problem).

What I changed using your help:
- In the function procedure:

Function prohibNH(astring As String)
    Dim Matches
    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")
    
    With regEx
     .Pattern = "^NH+"
    .IgnoreCase = False
    .Global = False
    End With
    
    Set Matches = regEx.Execute(astring)
    
    If Matches.Count = 0 Then
        prohibNH = astring
    Else
        prohibNH = ""
    End If
    
    Set regEx = Nothing
 End Function

- In the sub procedure:

Sub main()
 
 Dim dbs As DAO.Database
 Dim tbl As DAO.TableDef
 Dim rst As DAO.Recordset
 Dim sSql As String
 Dim strColumn1, strColumn2  As String
 
 Set dbs = CurrentDb()
 Set tbl = dbs.TableDefs("Names")
 
 sSql = "SELECT * FROM Names"

Set rst = dbs.OpenRecordset(sSql)

Do Until rst.EOF
rst.MoveFirst
strColumn1 = rst.Fields("NAMES")
strColumn2 = Trim(prohibNH(Trim(strColumn1)))
If Len(strColumn2) <> Null Then
    rst.Edit
    rst.Fields("REALNAMES").Value = strColumn2
    rst.Update
End If
rst.MoveNext
Loop[/COLOR]

End Sub

Do you see anywhere a problem? I suppose it must be somewhere in the loop as the program never responds..


Thanks a lot!!!! (I was unable to go further on this earlier as other urgent work was waiting)

 

[RoyVidar]

I don't know, but here's a couple of hints to speed up execution

in stead of initializing a new regular expression object per each iteration, declare a private module level variable, i e, in the declaration section of the module (just below the "Option..." statements)

Private mre as object

then, test whether it is initialized in the function

Function prohibNH(astring As String) as string

    if mre is nothing then
        Set mre = CreateObject("vbscript.regexp")
    end if

    With mre
        .Pattern = "^NH+"
        .IgnoreCase = False
        .Global = False
        if .test(astring) then
            prohibNH = astring
        end if
    End With
    
End Function

(typed, not tested)

When you open the recordset, I'd ensure I'd only got those starting with the letters "NH", regardless of case, which should reduse the necessity of looping - also don't do SELECT * FROM..., specify columns.

SELECT NAMES, REALNAMES FROM NAMES WHERE NAMES LIKE 'NH*'

Also, this test

If Len(strColumn2) <> Null Then

I think, will always evaluate to false. The length of a string can never be Null, it will be a number ranging from 0 to the max length of this string. The test I showed in my previous reply, will be true when the length is different from 0.

There is no need to use any tabledef, only openrecordset (but with my sql).

As the last line of the main sub, you can set mre = nothing

 

[Leen]

regular expressions

Hi,

.. it still bugs.. Actually, I tried everything you wrote and looked in the help but I still have no idea when the Access comes into "no responding" fase when I run the sub procedure.

If you have any other idea on how to solve this problem..
Anyway, thanks a lot for all help you have given!!
Leen

Beneath the final code I corrected to

Option Compare Database
Option Explicit
Private mre As Object

Function prohibNH(astring As String)
    If mre Is Nothing Then
        Set mre = CreateObject("vbscript.regexp")
    End If
    
    'Dim Matches
    'Dim regEx As Object
    'Set regEx = CreateObject("vbscript.regexp")
    
    With mre
        .Pattern = "^NH+"
        .IgnoreCase = False
        .Global = False
    If .test(astring) Then
        prohibNH = astring
    End If
    End With
    
 End Function

Sub main()
 
 Dim dbs As DAO.Database
 Dim tbl As DAO.TableDef
 Dim rst As DAO.Recordset
 Dim sSql As String
 Dim strColumn1, strColumn2  As String
 
 Set dbs = CurrentDb()
 Set tbl = dbs.TableDefs("Names")
 
 sSql = "SELECT Names,Realnames from Names"

Set rst = dbs.OpenRecordset(sSql)

Do Until rst.EOF
rst.MoveFirst
strColumn1 = rst.Fields("NAMES")
strColumn2 = Trim(prohibNH(Trim(strColumn1)))
If Len(strColumn2) Then
    rst.Edit
    rst.Fields("REALNAMES").Value = strColumn2
    rst.Update
End If
rst.MoveNext
Loop
mre = Nothing

End Sub

 

[RoyVidar]

Every time you start your loop, you move to the first record. If moving to the first record is necessary, do it outside (before) the loop. Sorry, I should have seen that the last time.

Again, you might want to add the where clause, which will ensure only records where this field starts with "nh" are fetched and treated.

Again, there is no need for the tabledef object, unless you're doing something else too.

Releasing the object would be

set mre = nothing

 

[Leen]

Thanks a million!!!!!!! It works!!!