By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,926 Members | 1,397 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,926 IT Pros & Developers. It's quick & easy.

RegExp Help Needed "Type Mismatch"

P: n/a
Trying to get Regular Expressions working.......

----------------
Public Function SepString(InField As String) As String
''seperates on space and comma

Dim RE As New RegExp
Dim Matches As Match

RE.IgnoreCase = True
RE.Pattern = "^(\w+)\s|,"
Set Matches = RE.Execute(InField) <<<----- "Type Mismatch"
SepString = Matches.Value

End Function
--------------------

Thanks in advance,

Christian

Mar 6 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Mar 6, 4:15 pm, "Davr...@gmail.com" <Davr...@gmail.comwrote:
Trying to get Regular Expressions working.......

----------------
Public Function SepString(InField As String) As String
''seperates on space and comma

Dim RE As New RegExp
Dim Matches As Match

RE.IgnoreCase = True
RE.Pattern = "^(\w+)\s|,"

Set Matches = RE.Execute(InField) <<<----- "Type Mismatch"
SepString = Matches.Value

End Function
--------------------

Thanks in advance,

Christian
You can avoid the Type Mismatch error by using

Dim Matches As MatchCollection

but then you'll get an error because Matches.Value is no longer a
valid property reference. Normally one iterates through the
MatchCollection as in

Dim thing as match
'
' ...stuff omitted...
'
For Each thing In Matches
Debug.Print thing.Value ' or some other useful activity
Next
What exactly do you pass to your function and what do you want it to
return?

Mar 6 '07 #2

P: n/a
On Mar 6, 3:47 pm, "Gord" <g...@kingston.netwrote:
What exactly do you pass to your function and what do you want it to
return?
opps. Sorry.

This is being used in a query.

SepString([Provider])

Where 'Provider' is a field in a table like:

"United Hospital"

Actually, the strings are more screwed up than that, which is why I'm
turning to regexpr.

This code:

SepString = RE.Replace(InField, InField)

works, but it's definitely not what I want it to do.

I just want it to return the one match. I've anchored it at the
beginning of the string.

Thanks,

Christian

Mar 6 '07 #3

P: n/a
OK. I got it to work with

Dim Matches As MatchCollection
Dim thing As Match

...and...

For Each thing In Matches
SepString = thing.Value ' or some other useful activity
Exit Function
Next

---------------------

I guess it would be useful to do multiple numbered matches, as in:

Public Function SepString(InField As Variant, Numb As Integer) As
String

where number is it's place in the match collection, but:

SepString = thing(Numb).Value

is not working.
Christian

Mar 6 '07 #4

P: n/a
On Mar 6, 4:57 pm, "Davr...@gmail.com" <Davr...@gmail.comwrote:
On Mar 6, 3:47 pm, "Gord" <g...@kingston.netwrote:
What exactly do you pass to your function and what do you want it to
return?

opps. Sorry.

This is being used in a query.

SepString([Provider])

Where 'Provider' is a field in a table like:

"United Hospital"

Actually, the strings are more screwed up than that, which is why I'm
turning to regexpr.

This code:

SepString = RE.Replace(InField, InField)

works, but it's definitely not what I want it to do.

I just want it to return the one match. I've anchored it at the
beginning of the string.

Thanks,

Christian
Try this:

Public Function SepString(InField As String) As String
'separates on space and comma, returns first submatch

Dim RE As New RegExp
Dim Matches As MatchCollection

RE.IgnoreCase = True
RE.Pattern = "^(\w+)(\s|,)"

Set Matches = RE.Execute(InField)

SepString = Matches(0).SubMatches(0)

Set Matches = Nothing
Set RE = Nothing
End Function

Mar 6 '07 #5

P: n/a
Beautiful! Thanks!
Christian

Mar 6 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.