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

Parsing

P: n/a
Could someone please point me to a function/module for Access 2000 that will
enable me to parse a string field. So if a field consists of "word_1
word_2 ...... word_n", I would like to be able to select either the first
or the last or any number of consecutive words from the middle (similar to
the 'mid' function for characters).

Many thanks

Dec 18 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
Split

Dec 18 '05 #2

P: n/a
Use the Split() function to break the words into an array.

Then LBound() gives you the first word, UBound() the last, or you can get
the nth element of the array for anything in between.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <ke******@peterbhurst.f2s.com> wrote in message
news:do**********@news.freedom2surf.net...
Could someone please point me to a function/module for Access 2000 that
will enable me to parse a string field. So if a field consists of
"word_1 word_2 ...... word_n", I would like to be able to select either
the first or the last or any number of consecutive words from the middle
(similar to the 'mid' function for characters).

Dec 18 '05 #3

P: n/a
Thanks, but I do not seem to have Split in Access 2000. Is there another
way or can I import it form somewhere?

Peter

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Use the Split() function to break the words into an array.

Then LBound() gives you the first word, UBound() the last, or you can get
the nth element of the array for anything in between.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter" <ke******@peterbhurst.f2s.com> wrote in message
news:do**********@news.freedom2surf.net...
Could someone please point me to a function/module for Access 2000 that
will enable me to parse a string field. So if a field consists of
"word_1 word_2 ...... word_n", I would like to be able to select either
the first or the last or any number of consecutive words from the middle
(similar to the 'mid' function for characters).


Dec 18 '05 #4

P: n/a
I don't seem to have the "split" function either...
Darn!!
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:43***********************@per-qv1-newsreader-01.iinet.net.au...
Use the Split() function to break the words into an array.

Dec 18 '05 #5

P: n/a
Peter wrote:
Thanks, but I do not seem to have Split in Access 2000. Is there
another way or can I import it form somewhere?


Split() was added in Access 2000. Are you trying to use it or trying to find it
in A2K's terrible help system?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Dec 18 '05 #6

P: n/a
Thanks for responding. I have been recommended to use split and L/UBound to
parse a string field in response to my request at the start of this thread.
No - I cannot find 'split' in this context in the on line help.
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:%k*******************@newssvr29.news.prodigy. net...
Peter wrote:
Thanks, but I do not seem to have Split in Access 2000. Is there
another way or can I import it form somewhere?


Split() was added in Access 2000. Are you trying to use it or trying to
find it in A2K's terrible help system?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Dec 18 '05 #7

P: n/a
Peter wrote in message <do**********@news.freedom2surf.net> :
Thanks for responding. I have been recommended to use split and L/UBound to
parse a string field in response to my request at the start of this thread.
No - I cannot find 'split' in this context in the on line help.

In VBE (hit Ctrl + g) type Split, then hit F1.

Then, if you also give use some bits and pieces about the purpose
of what you're doing, we might event help;-)

--
Roy-Vidar

Dec 18 '05 #8

P: n/a
Is there a great roll-your-own split function out there. I cobbled one
together yesterday because an e-mail I got asking about same was so
"quaint"!

But it's a few minutes work. Do we have a tested quick one?

Someone in e-mail wrote:
Hello, very much pleasure of greeting yourself!

I have a problem with the code in the module sendmailwithOE.
It is the function "split"
I cannot find her.
Can you send her me?

Thank you very much


Reply:

Hello

Split is a function introduced in Access 2000.

I am guessing that you are using Access 97.

I have taken a few minutes to write a similar function:

Public Function SplitB(ByVal SplitString As String, ByVal Delimiter As
String) As Variant
Dim Position As Long
Dim aSplit() As Variant
Dim Dimension As Long
Position = InStr(SplitString, Delimiter)
Do While Position <> 0
ReDim Preserve aSplit(Dimension)
aSplit(UBound(aSplit)) = Trim(Left(SplitString, Position - 1))
SplitString = Mid$(SplitString, Position + 1)
Dimension = Dimension + 1
Position = InStr(SplitString, Delimiter)
Loop
ReDim Preserve aSplit(Dimension)
aSplit(Dimension) = Trim(SplitString)
SplitB = aSplit
End Function

You could try this, but it is untested and I cannot say that it will
work.

You will, of course, have to replace the "Split" with "SplitB" in your
other code.

How much does a villa cost, anyway?

Lyle Fairfield

Dec 18 '05 #9

P: n/a
> In VBE (hit Ctrl + g) type Split, then hit F1.

Then, if you also give use some bits and pieces about the purpose
of what you're doing, we might event help;-)

--
Roy-Vidar

I have found split within VBA (I Was looking in the build facility for
Queries). This is a copy of my original question:
Could someone please point me to a function/module for Access 2000 that
will enable me to parse a string field. So if a field consists of
"word_1 word_2 ...... word_n", I would like to be able to select either
the first or the last or any number of consecutive words from the middle
(similar to the 'mid' function for characters).


I am no programing wiz, so I need all the help that I can get please!
Dec 18 '05 #10

P: n/a
Peter, here's another example I put together for someone else who was trying
to type a bunch of keywords in a text box, and then filter a form where the
Notes field contained any of them.

Hopefully you can adapt the approach.

Private Sub txtKeywords_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant 'Array of keywords.
Dim i As Integer
Dim lngLen As Long

If IsNull(Me.txtKeywords) Then 'Show all if blank.
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
varKeywords = Split(Me.txtKeywords, " ")
If UBound(varKeywords) >= 99 Then '99 words max.
MsgBox "Too many words."
Else
'Build up the Where string from the array.
For i = LBound(varKeywords) To UBound(varKeywords)
strWord = Trim$(varKeywords(i))
If strWord <> vbNullString Then
strWhere = strWhere & "([Notes] Like ""*" & strWord &
"*"") OR "
End If
Next
lngLen = Len(strWhere) - 4 'Without trailing " OR ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Peter" <ke******@peterbhurst.f2s.com> wrote in message
news:do**********@news.freedom2surf.net...
In VBE (hit Ctrl + g) type Split, then hit F1.

Then, if you also give use some bits and pieces about the purpose
of what you're doing, we might event help;-)

--
Roy-Vidar


I have found split within VBA (I Was looking in the build facility for
Queries). This is a copy of my original question:
Could someone please point me to a function/module for Access 2000 that
will enable me to parse a string field. So if a field consists of
"word_1 word_2 ...... word_n", I would like to be able to select either
the first or the last or any number of consecutive words from the middle
(similar to the 'mid' function for characters).


I am no programing wiz, so I need all the help that I can get please!

Dec 18 '05 #11

P: n/a
Peter wrote in message <do**********@news.freedom2surf.net> :
I have found split within VBA (I Was looking in the build facility for
Queries). This is a copy of my original question:
Could someone please point me to a function/module for Access 2000 that
will enable me to parse a string field. So if a field consists of "word_1
word_2 ...... word_n", I would like to be able to select either the first
or the last or any number of consecutive words from the middle (similar to
the 'mid' function for characters).


I am no programing wiz, so I need all the help that I can get please!


Well, here's an attempt, which I think you should be able to use for
instance in a query - note however, it's not tested in VBA.

Public Function mySplit(ByVal v_strInString As String, _
ByVal v_lngOrder As Long, _
Optional ByVal v_lngNumber As Long = 1, _
Optional ByVal v_strDel As String = ", ") _
As String

' v_strInString - the string to delimit
' v_lngOrder 0 = last word
' 1-N = index of word one wishes
' v_lngNumber 1-N = optional number of consecutive words
' wished, if not provided 1 is default
' v_strDelm = optional delimiter used for returned
' string, ", " is default

Dim myarr() As String
Dim lngCounter As Long
Dim lngMax As Long

myarr = Split(v_strInString, " ")
lngMax = Ubound(myarr) + 1
If v_lngOrder >= lngMax Then
v_lngOrder = lngMax
End If
Select Case v_lngOrder
Case 0
mySplit = myarr(lngMax - 1)
Case Else
If v_lngNumber > 1 Then
If v_lngOrder + v_lngNumber < lngMax Then
lngMax = v_lngOrder + v_lngNumber
End If
For lngCounter = v_lngOrder To lngMax - 1
mySplit = mySplit & myarr(lngCounter) & v_strDel
Next
mySplit = Mid$(mySplit, 1, Len(mySplit) - Len(v_strDel))
Else
mySplit = myarr(v_lngOrder - 1)
End If
End Select
End Function
But, allow one comment - if you're storing information in a field
such as you say, it does seem like a violation of first normal
form, and that it perhaps could be better solved with another
table?

--
Roy-Vidar

Dec 18 '05 #12

P: n/a

From Joe "/We Are Borg/" Foster, several years ago. I use this quite
a bit, even in A2K and above. (I seems to work quite well....)

Public Function JoeSplit(ByVal Expression As String, _
Optional ByVal Delimiter As String = " ", _
Optional ByVal Start As Long = 1, _
Optional ByVal Limit As Long = -1, _
Optional ByVal SkipEmptyFields As Boolean = False, _
Optional ByVal vCompare = vbBinaryCompare) _
As Variant

Dim LE As Long: LE = Len(Expression)
Dim LD As Long: LD = Len(Delimiter)

Dim Result() As String
Dim RCount As Long, RMax As Long

' greasy VB6 compatibility stuff
If Limit < -1 Or Start < 1 Then
Err.Raise 5
ElseIf LD > 0 And (LE = 0 Or Limit = 0) Then
JoeSplit = Array()
Exit Function
ElseIf LD = 0 Or Limit = 1 Then
ReDim Result(0 To 0) As String
If Start <> 1 Then Result(0) = Mid$(Expression, Start) _
Else Result(0) = Expression 'StrSwap Result(0), Expression
JoeSplit = Result
Exit Function
End If

If SkipEmptyFields Then RMax = (LE - Start + 1) \ (LD + 1) _
Else RMax = (LE - Start + 1) \ LD
If Limit < 1 Or Limit > 1000 Then _
ReDim Result(0 To RMax) As String _
Else ReDim Result(0 To Limit - 1) As String

Dim Pos1 As Long, Pos2 As Long
Pos1 = Start
Do
If Limit = 1 Then
Pos2 = LE + 1
ElseIf vCompare = vbBinaryCompare Then
Pos2 = InStr(Pos1, Expression, Delimiter, vbBinaryCompare)
Limit = Limit - 1
If Pos2 = 0 Then Pos2 = LE + 1
Else ' slimy performance hack, probably could be improved
' Microsoft has absolutely no excuse whatsoever for making
' this sort of shite necessary or even at all beneficial!
Dim C As Long, p As Long
C = LD * 4
Pos2 = Pos1
Do
p = InStr(1, Mid$(Expression, Pos2, C), Delimiter, vCompare)
If p > 0 Then Pos2 = Pos2 + p - 1: Exit Do
Pos2 = Pos2 + C - 2 * LD ' beware of chr(230) and others!
If C < 600 Then C = C + C \ 2
If Pos2 > LE Then Pos2 = LE + 1: Exit Do
Loop
Limit = Limit - 1
End If

If Pos1 < Pos2 Then
Result(RCount) = Mid$(Expression, Pos1, Pos2 - Pos1)
RCount = RCount + 1
ElseIf SkipEmptyFields = False Then
RCount = RCount + 1
End If

Pos1 = Pos2 + LD
Loop While Pos2 <= LE

If RCount = 0 Then
JoeSplit = Array(): Exit Function
ElseIf RCount <= UBound(Result) Then
ReDim Preserve Result(0 To RCount - 1) As String
End If

JoeSplit = Result
End Function

On 18 Dec 2005 06:47:21 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:
Is there a great roll-your-own split function out there. I cobbled one
together yesterday because an e-mail I got asking about same was so
"quaint"!

But it's a few minutes work. Do we have a tested quick one?

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Dec 19 '05 #13

P: n/a
But, allow one comment - if you're storing information in a field
such as you say, it does seem like a violation of first normal
form, and that it perhaps could be better solved with another
table?

--
Roy-Vidar

Just to respond to this point, you are probably right. My databases are
strictly for my use and I do not have the time or skill for elegance. The
field in question is 'Place', which can contain any number of words for
which the list is virtually inexhaustible! I have a 'Country' field which
is referenced from a table. The 'Place' field is structured to show
County/State/Region/, City/Area etc up to some 5 to 10 words. The
combinations are too numerous and complex to put into a referenced table.
So if I wish to filter on 'County'. I need to isolate first word, or if on
city, on mid word etc. There is probably a better, more elegant way of
doing this. There is the difficulty over entities with double barrelled
names (e.g. North Yorkshire), which I enter with an underscore
(North_Yorkshire), but possibly some function which selects word between
standard delimiters might work better.

Peter
Dec 19 '05 #14

P: n/a

Thanks ever one for being so helpful. I will try out the various
suggestions.

Peter
Dec 19 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.