Find start position of the 9th occurance of a <character> in a str

[Mikael Lindqvist]

Good morning everyone,

I know that the function "Instr" is very useful for finding starting
position of a particular "string" in a string.

However, in my case I have a text field that contains up to 9 values that I
want to extract, each value is seperated by a semicolon.

So I figured I create 9 queries, extracting the 1st, 2nd, 3rd,....9th value
from the master string. Then I'll have a "union query" for all 9 queries to
normalize the values in a tableform.

Now to my question: While it's straightforward to search for first and
second occurance of a string, after that the query grows really big... here's
the query for 2nd occurance of ";".

Instr(Instr([NotesTxt];";")+1;[NotesTxt];";")

After that I guess I'll just keep nesting the "Instr" function... but I hope
there's a more simple way to find the starting position for ";" than using
this kind of "nested" logic???

Any help as always much appreciated!

Kindly,
Mikael
Sweden

 

[John Spencer]

Simpler would be to use a custom VBA function to do this. Copy and paste the
function below into a VBA module and then you can call it in your query.

In your query you would use
Val1: getSection([NotesTxt],";",1)
....
Val9: getSection([NotesTxt],";",9)

Public Function getSection(strIn, _
Optional strDelimiter As String = ";", _
Optional intSectionNumber As Integer = 1)
'==============================================================
' Procedure : getSection
' Author : John Spencer
' Purpose : Return section of string
'Parses string into sections and returns the specified section
' / Item / token from the string
' Access 2000 and later
'==============================================================

Dim strArray As Variant

If Len(strIn & vbNullString) = 0 Then
getSection = strIn
Else
strArray = Split(strIn, strDelimiter, -1, vbTextCompare)

If UBound(strArray) >= intSectionNumber - 1 Then
getSection = strArray(intSectionNumber - 1)
Else
getSection = Null
End If

End If

End Function


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Mikael,
You can create a small function that parses the occurances. Open a new,
blank module and paste this function into it:

Function SplitString(strIn As String, strDelim As String, _
intPart As Integer, Optional booTrim As Boolean = True) As String
Dim Ary
'Arrays are zero based
intPart = intPart - 1
Ary = Split(strIn, strDelim)
If intPart >= 0 And intPart <= UBound(Ary) Then
If booTrim Then
SplitString = Trim(Ary(intPart))
Else
SplitString = Ary(intPart)
End If
Else
SplitString = ""
End If
End Function

The save the module with the name "modStringFunctions". You can now parse
your separate values in a query with the function like:
Part1: SplitString([NotesTxt],";",1)
and
Part2: SplitString([NotesTxt],";",2)
etc