Getting the middle bit out

[Robert Gillard]

I have a field of peoples names plus a subject

Gillard, Robert ; Sports
Smith, Ian ; Fashion

I would like to be able to split the 3 out - surname, first name, section.
( but particularly the first name which appears in the middle and the
surname which appears first) as I would like to use them in a mailing
heading i.e..
"Dear Robert Gillard"

Obviously the names are all of different lengths, could somebody please
advise how I can do this.

with thanks

Bob

 

[Rick Brandt]

I have a field of peoples names plus a subject

Gillard, Robert ; Sports
Smith, Ian ; Fashion

I would like to be able to split the 3 out - surname, first name, section.
( but particularly the first name which appears in the middle and the
surname which appears first) as I would like to use them in a mailing
heading i.e..
"Dear Robert Gillard"

Obviously the names are all of different lengths, could somebody please
advise how I can do this.

If your example format is consistent...

Dim CommaPosition as Integer
Dim SemiPosition as Integer
Dim FirstNameLen as Integer
Dim FirstName as String
Dim LastName as String
Dim Section as String

CommaPosition = InStr(1, [YourFieldName], ",")
SemiPosition = InStr(1, [YourFieldName], ";")
FirstNameLen = (SemiPosition-CommaPosition)-3

FirstName = Mid([YourFieldName], CommaPosition+2, FirstNameLen)
LastName = Left([YourFieldName], CommaPosition-1)
Section = Mid([YourFieldName], SemiPosition+2)

 

[John Nurick]

Hi Robert,

If the fields are reliably laid out as per your samples, with
comma-space between last and first names and a space-semicolon-space
between first name and subject, it's quite easy. Just use calculated
fields like these in a query (these expressions are untested and you may
need to adjust them slightly). Replace X with the name of your field:

Surname: IIF(IsNull([X]), Null, Left([X], InStr([X], ", ") - 1)

FirstName: IIF(IsNull([X]), Null, Mid([X], InStr([X], ", ") + 2,
InStr([X], " ; ") - InStr([X], ", ") - 2))

Subject: IIF(IsNull[X], Null, Mid([X], InStr([X], " ; ") + 3))