Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strip Characters (A2k)

    We have a field within our (Odbc) database containing a string of Operator Initials separated (99% of the time) by the "/" character.

    The string may look like DW/MR/KAZ/DL or KAZ/DL/MR/DL/AC
    How can I break this string down and separate each operators initials ?
    I would like to use the stripped out initials later in reports.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip Characters (A2k)

    Hi Dave,

    If you split DW/MR/KAZ/DL, you end up with four sets of initials. Where do you want to store them?

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Hans

    The second example is 5 sets of initials, its a set format which everyone knows to separate with "/".
    Where to store them ? I suppose the function would have to store them for query use.
    Example Attached:

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip Characters (A2k)

    So everyone has initials DW? Why bother then? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    I think we're talking at cross purposes. Could you try to explain more clearly what you want to accomplish?

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Sorry Hans

    Didn't check the query before pasting.
    See below:

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip Characters (A2k)

    You could do the following:
    1) Put the following function in a module:
    <code>
    Public Function SplitPart(aString, n As Long)
    SplitPart = Null
    On Error Resume Next
    SplitPart = Split(aString, "/")(n - 1)
    End Function
    </code>
    2) Create a query based on your table.
    Add the fields you need, plus calculated columns
    <code>
    Initials1: SplitPart([Initials],1)

    Initials2: SplitPart([Initials],2)
    </code>
    etc., where Initials is the name of the field containing the concatenated initials. You'll have to add as many columns as are conceivably ever needed. It's not very elegant, but theoretically better approaches are probably not feasible since you're using a linked table.

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Hans <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    That is so elegant, thanks.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Hans

    Just a little glitch here, if only one person has entered their initial (Meaning they are the only person to handle the case)then that initial really needs to be shown in the query.
    See the attached PNG, ignore the centre column.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip Characters (A2k)

    It looks like you omitted to include a column for the first initials. It should look like this:

    Initials1: SplitPart([EST_NME],1)

    If you do have that, make sure that the SplitPart function has

    SplitPart = Split(aString, "/")(n - 1)

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Hans

    I have all the code correct, and the column (In this particular case) isn't required.
    Using the [Initials1: SplitPart([EST_NME],1)] I realise I can strip all the characters ie

    Initials1: SplitPart([EST_NME],1)
    Initials2: SplitPart([EST_NME],2)
    Initials3: SplitPart([EST_NME],3)
    Initials4: SplitPart([EST_NME],4)

    In fact the function can be used to however many initials and "/" characters are in the string, but, what I was trying to get across is if only one set of initials are in the string ie
    in the attached PNG [MR] or [MW] there are no "/" characters so the function ignores the initial.

    If the string is [DW/MR/KAZ] then column 1,2,3 would be [DW] [MR] [KAZ] respectfully, if the string is [DW] then that should also be returned possibly in all the columns regardless.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip Characters (A2k)

    I don't understand. If there is only one initial, and no slash, the function should return the initial. See attached demo.

    Why would you want to fill all columns with the same initial?

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Hans

    Your example works exactly the same, and it's exactly what I was after., there is no issue with the way it works up to a point.

    The query criteria:

    Initials1: SplitPart([EST_NME],1)
    Initials2: SplitPart([EST_NME],2)
    Initials3: SplitPart([EST_NME],3)
    Initials4: SplitPart([EST_NME],4)

    I will be using as:

    Estimator: SplitPart([EST_NME],1)
    FileHandler: SplitPart([EST_NME],2)
    Inputter: SplitPart([EST_NME],3)
    Negotiator: SplitPart([EST_NME],4)

    If I have DW/KAZ/DL/DP then I know DW is the estimator, KAZ is the file handler, DL is the inputter and DP has negotiated the case, perfect, but, if only one initial is in the string ie [DW] that tells me that DW is the only person involved with this claim, so he could have estimated or be the FileHandler or input or negotiated it.
    If the single Initial only goes into column1 (Estimator) then I can't run a report on him even though he is the FileHandler,Inputter aand possibly negotiator because he is only in column1

    I know it's difficult to explain but it's got to be this way.
    Unfortunately our ancient management system doesn't allow us the luxery of a separate field for each, this is why we have to use this format.
    Sorry to be a pain.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strip Characters (A2k)

    Try this modified version of the function:

    Public Function SplitPart(aString, n As Long)
    Dim arr() As String
    SplitPart = Null
    On Error GoTo GetOut
    arr = Split(aString, "/")
    If UBound(arr) = 0 Then
    SplitPart = arr(0)
    Else
    SplitPart = arr(n - 1)
    End If
    GetOut:
    End Function

    (I still don't understand why you got nothing in Initials1 if there was only one initial)

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strip Characters (A2k)

    Hans
    Thanks, the updated function works as it needs to now.

    <font color=blue>(I still don't understand why you got nothing in Initials1 if there was only one initial)</font color=blue>

    It was actually column2 in the above PNG, the report was for FileHandler so column1(Estimator) wasn't required for that particular report.

    Anyway, we're sorted now, thanks once more.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •