Re: unconcatenated

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Try this:

'******Start of code*******
Public Function fGetFirstChars_Nums_w(pString As Variant) As String
Dim tmp As String
Dim strRemStr As String
Dim strNxtChar As String
Dim strPrevChar As String
Dim strW As String
Dim bytChrLoc As Byte
Dim bytWLoc As Byte
Dim bytRemLen As Byte
Dim bytStrLen As Byte
Dim strHyphen As String
Dim cntr
Dim NoNumber As Boolean
Dim NoW As Boolean

'set the values of the flags
NoNumber = False
NoW = False
If Len(Trim(pString & "")) > 0 Then
'rule "B" - if the string contains a "/"
bytChrLoc = InStr(1, pString, "/")
If bytChrLoc > 0 Then
'the following code will loop until the
'previous character is numberic
FindLastNum:
'Character before the "/" must be a number
strPrevChar = Mid(pString, bytChrLoc - 1, 1)
If IsNumeric(strPrevChar) Then
tmp = Left(pString, bytChrLoc - 1)
'next check for any number following the "/"
strRemStr = Right(pString, Len(pString) - bytChrLoc)
bytRemLen = Len(strRemStr)
For cntr = 1 To bytRemLen
strNxtChar = Mid(strRemStr, cntr, 1)
If IsNumeric(strNxtChar) Then
strNxtChar = Mid(strRemStr, cntr, 1)
tmp = tmp + "/" + strNxtChar
GoTo ChkForLetterW
End If
Next cntr
If cntr = bytRemLen + 1 Then
NoNumber = True
End If
ChkForLetterW:
'check to see if the letter "W" exists
'in the remainin g string
bytWLoc = InStr(1, strRemStr, "W")
If bytWLoc > 0 Then
'read the "W" string from the string (no matter
' if it is a capital "W" or not it will still be
' the same character
strW = Mid(strRemStr, bytWLoc, 1)
tmp = tmp + strW
Else
NoW = True
End If
'rule "B-2" - if there is no number following the "/" and
' there is no "W" following the "/"
'use rule "A"
If NoNumber = True And NoW = True Then
GoTo RuleA
End If
'the rules for "B" have been applied and the string is ready
GoTo ReturnString
Else
'try to find the last number in the string
bytChrLoc = bytChrLoc - 1
GoTo FindLastNum
End If
Else
tmp = pString
End If

'rule "C" - if the string contains a "-"
bytChrLoc = InStr(1, tmp, "-")
If bytChrLoc > 0 Then
strHyphen = Right(tmp, Len(tmp) - (bytChrLoc - 1))
tmp = Left(tmp, bytChrLoc - 1)
Else
strHyphen = ""
End If
RuleA:
'rule "A" - String must end with a number except when
' there is a "W" in the string
bytChrLoc = InStr(1, tmp, "w")
If bytChrLoc > 0 Then
tmp = Left(tmp, bytChrLoc)
Else
strPrevChar = Right(tmp, 1)
If strPrevChar = "w" Then
'read the "W" string from the string (no matter
' if it is a capital "W" or not it will still be
' the same character
strW = Right(tmp, 1)
tmp = tmp + strW
Else
If IsNumeric(strPrevChar) Then
GoTo ReturnString
Else
cntr = 1
ChkPrevChr:
strPrevChar = Mid(tmp, Len(tmp) - cntr, 1)
If IsNumeric(strPrevChar) Then
tmp = Left(tmp, Len(tmp) - cntr)
Else
'decrement the "bytStrLen" variable
If cntr > 0 Then
cntr = cntr + 1
GoTo ChkPrevChr
End If
End If
End If
End If
End If
If strHyphen > "" Then
tmp = tmp + strHyphen
Else
tmp = vbNullString
End If
End If

ReturnString:
fGetFirstChars_Nums_w = tmp
End Function
'******End of Code******

I did test each of the values you posted and this funciton will return the
values you are looking for in each case.

There is no error handling code and you may need to evaluate any instances
where the characters in the string may not be in the exact order.

--
HTH

Mr B


"Haggr via AccessMonster.com" wrote:

I get data from an external source (beyond my control) that I use in my
program. One field is a concatenated string. I need to unconcatenat that
string to get to a (root item number). That program is written in "Cobol"

Rule A: String should end with a number, except when there is a "W" then
should end in "W"
ex (xlb114r) = (xlb114) or (xlb114wr) = (xlb114w)

Rule B: If string contain "/" , string before "/" should end with a number,
then the "/" and any number and/or "W"
ex (xmr115/4sy) = (xmr115/4) or (amr115/4syw) = (xmr115/4w)

Rule B-2: If after "/" there is not any numbers and/or "W" the apply Rule A
without the "/"
ex (xs12pt/aa) = (xs12) or (xs12pt/aaw) = (xs12w) "notice no "/"

Rule C: string with "-", Apply Rule A and keep "-" and everthing after "-"
ex (xan72aa-7.5) = (xan72-7.5)

Here is what I have so far. This handles Rule A fine, but not the others

Public Function fGetFirstChars_Nums_w(pString As Variant) As String
On Error GoTo Err_fGetFirstCharsNums
Dim i As Integer
Dim boolNum As Boolean
Dim ch As String
Dim tmp As String

If Len(Trim(pString & "")) > 0 Then
For i = 1 To Len(pString)
ch = MID(pString, i, 1)
Select Case ch
Case "0" To "9"
If boolNum = False Then boolNum = True
tmp = tmp & ch
Case "w"
tmp = tmp & ch
If boolNum = True Then Exit For
Case "-", "/"
tmp = tmp & ch & tmp
If boolNum = True Then Exit For
Case Else
If boolNum = False Then
'no number char yet
tmp = tmp & ch
Else
Exit For
End If
End Select

Next
Else
tmp = vbNullString
End If

fGetFirstChars_Nums_w = tmp

Exit_fGetFirstCharsNums:
Exit Function

Err_fGetFirstCharsNums:
MsgBox Err.Description
Resume Exit_fGetFirstCharsNums
End Function



Douglas J. Steele wrote:
This newsgroup is manned by humans, not machines.

Perhaps you can explain what it is you're looking for in a little more
detail...

unconcatenated and concatenated string

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200702/1


.

Relevant Pages

  • Re: unconcatenated
    ... Public Function fGetFirstChars_Nums_w(pString As Variant) As String ... Dim tmp As String ... Dim tmpStr As String ... For cntr = 1 To bytRemLen ...
    (microsoft.public.access.modulesdaovba)
  • Re: unconcatenated
    ... Public Function fGetFirstChars_Nums_w(pString As Variant) As String ... Dim tmp As String ... Dim tmpStr As String ... For cntr = 1 To bytRemLen ...
    (microsoft.public.access.modulesdaovba)
  • Re: unconcatenated
    ... Public Function fGetFirstChars_Nums_w(pString As Variant) As String ... Dim tmp As String ... Dim tmpStr As String ... For cntr = 1 To bytRemLen ...
    (microsoft.public.access.modulesdaovba)
  • RE: Custom VBA Function to sort an array of values.
    ... Dim List() As String ... List= "4 FREPOW" ... Public Function CollapseListAs String) As String ... Dim tmp As String ...
    (microsoft.public.excel.programming)
  • Re: wildcard search
    ... Public Function fGetFirstChars_Nums_w(pString As Variant) As String ... Dim boolNum As Boolean ... Dim tmp As String ... Result after adding Immediate Window "Ambiguious Name Found fGetNums ...
    (microsoft.public.access.queries)