New to VBA, Can't figure out this Do While/If Then Problem

[tford06]

I'm new to VBA, as in I've been trying to learn it to solve a problem over the past 2 days. I have experience in Java so that seems to have kind of helped.

Anyways, the problem I'm trying to solve is extracting varying lengths of text between numbers in a string. I want to be able to use a function in Access that allows me to do something like this:

Term1: pullTerm([termString], 3) where the number would be the 3rd term in a series.

Here's the code. As of right now my character index variable keeps incrementing but never exiting the first nested loop.

Any ideas would be very helpful.

Public Function pullTerm(strInput As String, returnValue As Integer) As String

Dim term1Start As Integer
Dim term1End As Integer
Dim term1 As String
Dim term2Start As Integer
Dim term2End As Integer
Dim term2 As String
Dim term3Start As Integer
Dim term3End As Integer
Dim term3 As String
Dim term4Start As Integer
Dim term4End As Integer
Dim term4 As String
Dim term5Start As Integer
Dim term5End As Integer
Dim term5 As String
Dim charIndex As Long

charIndex = 1
term1Start = 0
term1End = 0
term2Start = 0
term2End = 0
term3Start = 0
term3End = 0
term4Start = 0
term4End = 0
term5Start = 0
term5End = 0

Do While term1Start = 0

If IsNumeric(Mid(strInput, charIndex, 1)) = True Then
charIndex = charIndex + 1

Else: term1Start = charIndex And charIndex = charIndex + 1
Do While term1End = 0

If Not IsNumeric(Mid(strInput, term1Start + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term1End = charIndex And charIndex = charIndex + 1

End If
Loop

End If
Loop

Do While term2Start = 0

If IsNumeric(Mid(strInput, term1End + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term2Start = charIndex And charIndex = charIndex + 1
Do While term2End = 0

If Not IsNumeric(Mid(strInput, term2Start + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term2End = charIndex And charIndex = charIndex + 1

End If
Loop

End If
Loop

Do While term3Start = 0

If IsNumeric(Mid(strInput, term2End + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term3Start = charIndex And charIndex = charIndex + 1
Do While term3End = 0

If Not IsNumeric(Mid(strInput, term3Start + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term3End = charIndex And charIndex = charIndex + 1

End If
Loop

End If
Loop

Do While term4Start = 0

If IsNumeric(Mid(strInput, term3End + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term4Start = charIndex And charIndex = charIndex + 1
Do While term4End = 0

If Not IsNumeric(Mid(strInput, term4Start + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term4End = charIndex And charIndex = charIndex + 1

End If
Loop

End If
Loop

Do While term5Start = 0

If IsNumeric(Mid(strInput, term4End + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term5Start = charIndex And charIndex = charIndex + 1
Do While term5End = 0

If Not IsNumeric(Mid(strInput, term5Start + 1, 1)) = True Then
charIndex = charIndex + 1
Else: term5End = charIndex And charIndex = charIndex + 1

End If
Loop

End If
Loop

term1 = Mid(strInput, term1Start, (term1End - term1Start))
term2 = Mid(strInput, term2Start, (term2End - term2Start))
term3 = Mid(strInput, term3Start, (term3End - term3Start))
term4 = Mid(strInput, term4Start, (term4End - term4Start))
term5 = Mid(strInput, term5Start, (term5End - term5Start))

If returnValue = 1 Then returnTerm = term1
If returnValue = 2 Then returnTerm = term2
If returnValue = 3 Then returnTerm = term3
If returnValue = 4 Then returnTerm = term4
If returnValue = 5 Then returnTerm = term5

End Function

 

[JHB]

..
Anyways, the problem I'm trying to solve is extracting varying lengths of text between numbers in a string. I want to be able to use a function in Access that allows me to do something like this:

Term1: pullTerm([termString], 3) where the number would be the 3rd term in a series.

Here's the code. As of right now my character index variable keeps incrementing but never exiting the first nested loop.

Without the actually database and in the way your code is posted, (missing indenting of codelines), it isn't easy to spot what is wrong. When you post code use the code tags = "#".
Could you show some text sample on strInput and what you want to get out.

 

[gemma-the-husky]

there surely is an easier way.

give us an example of a string, and the bit you are trying to extract.

 

[tford06]

Well thanks for the replies but I solved the problem and have it working nicely.

But for anyone interested example strings would be.

~40,product one~0,misspelled product~0,item~0,~3

varying length product~0,product one~0,product 137~5,item2~14