Breaking Down a field

  • Thread starter Thread starter Box 666
  • Start date Start date
B

Box 666

I have a field that is populated as follows

Gillard, Robert J : Retail

The surname always has a comma after it and whilst there is not always an
initial after the 1st name there is always a space and a colon after it. I
need to be able to extract both the surname and 1st name to use in an
unbound text box.

Is it possible to extract this information from the above field, if so ,
how

with thanks

Bob
 
The functions InStr, Left and Mid should be enough.

It's not clear to me whether you want surname and 1st name in separate
boxes, or you simply want, in essence, to throw away the colon and what's
after it.

To get only what's in front of the colon, use

Trim(Left(FullText, InStr(FullText, ":") - 1))

To get only the surname (i.e.: what's in front of the colon), use

Trim(Left(FullText, InStr(FullText, ",") - 1))

To get what's between the comma and the colon, use

Trim(Mid(Trim(Left(FullText, InStr(FullText, ":") - 1)),
InStr(Trim(Left(FullText, InStr(FullText, ":") - 1)), ",") + 1))

(watch out for word-wrap: each of the statements above is supposed to be a
single line. Replace FullText with whatever's appropriate)
 
Doug,

Thank you just what I needed.

Bob

Douglas J. Steele said:
The functions InStr, Left and Mid should be enough.

It's not clear to me whether you want surname and 1st name in separate
boxes, or you simply want, in essence, to throw away the colon and what's
after it.

To get only what's in front of the colon, use

Trim(Left(FullText, InStr(FullText, ":") - 1))

To get only the surname (i.e.: what's in front of the colon), use

Trim(Left(FullText, InStr(FullText, ",") - 1))

To get what's between the comma and the colon, use

Trim(Mid(Trim(Left(FullText, InStr(FullText, ":") - 1)),
InStr(Trim(Left(FullText, InStr(FullText, ":") - 1)), ",") + 1))

(watch out for word-wrap: each of the statements above is supposed to be a
single line. Replace FullText with whatever's appropriate)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



it.
Click to expand...
 
Back
Top