Query -- Extract string between two characters ("," and "~")

[skydivetom]

Experts:

I need some assistance with an **IN BETWEEN TWO CHARACTERS** query.

Below are two (2) sample strings which include variable length last names, first names, and ranks:
Jackson, Tom ~ PO2 (E5)
Smith, Mary Ann ~ PO1 (E6)

- All last names and first names are separated by a comma (,)
- All first names and ranks are separated by a tilde (~)



Now, I figured out how to extract both the last name and rank title. However, I have NOT successfully extracted the variable length first name.

LastName: Left([CHOPS],InStr([CHOPS],", ")-1):
Jackson
Smith

RankTitle: Mid([CHOPS],InStr([CHOPS],"~")+2)
PO2 (E5)
PO1 (E6)

For first name though, I tried the following: FirstName: Mid([CHOPS],InStr([CHOPS],",")+2,Len([CHOPS])-InStr([CHOPS]," ~ ")-1)

I only provided these two examples (Tom, Ann); unfortunately, based on my current data set, the names are something chopped of though. How to I modify the FirstName query so that I get all characters between the "," and "~" (without leading/trailing spaces before/after name?
Please keep in mind that some first names have blank space (e.g., Mary Ann) while others may be hyphenated (Jean-Pierre).


Thank you,
EEH

[ranman256]

'loop thru all records
While Not .EOF
    'get vLine
    vLine  = 'get line here

    i = InStr(vLine, "~")
    While i > 0
        vName = Left(vLine, i - 1)
        vLine = Mid(vLine, i + 1)
        
        'now with vName, separate last & first
        i = InStr(vName, ",")
        vLast = Left(vName, i - 1)
        vFirst = Left(vName, i + 1)
        
          'get next name
        i = InStr(vLine, "~")
    Wend
    .MoveNext
Wend

[CJ_London]

you nearly had it right with

FirstName: Mid([CHOPS],InStr([CHOPS],",")+2,Len([CHOPS])-InStr([CHOPS]," ~ ")-1)

modify to this

FirstName: trim(left(mid([CHOPS],InStr([CHOPS],",")+1),InStr([CHOPS],"~")-1))

[skydivetom]

RANMAN: I prefer to use a simple query statement (vs. VBA function). Just like I did for lastname and rank. Back to my question:

Do you know how to modify "FirstName: Mid([CHOPS],InStr([CHOPS],",")+2,Len([CHOPS])-InStr([CHOPS]," ~ ")-1) so that I get all characters between comma and tilde?

[skydivetom]

Ajax:

Thank you for the support. I plugged it in and I get the following sample results:

Mike ~ PO2 (
Patrick ~ PO1
Kenneth ~ PO2 (E5
Mary ~ PO2

Instead, I should only have:
Mike
Patrick
Kenneth
Mary

How should this be tweak to only get everything until the ~ (-1 character for blank space)?

Tom

[CJ_London]

my bad

trim(left(mid([CHOPS],InStr([CHOPS],",")+1),InStr(mid([CHOPS],InStr([CHOPS],",")+1),"~")-1))

[skydivetom]

Ajax -- it works perfectly now!! Thank you so much for the help.

[CJ_London]

happy to help

[accesstos]

Just for the challenge.

Trim(Mid(Left([CHOPS],InStrRev([CHOPS],"~")-1),InStrRev([CHOPS],",")+1))

Saves one mid() and one +1.

[CJ_London]

good one - have you noticed how often by reversing the logic, the code becomes simpler and/or shorter?

[accesstos]

Oh yes!
Always, we have to keep alive our deviant thinking, the childhood creative ability that we lose as we grow older.

[skydivetom]

Thank you for the valuable contributions... very much appreciated.