Returning the first character of each word in a field

  • Thread starter Thread starter Kristina
  • Start date Start date
K

Kristina

I have a field that houses a first and last name (I don't
want them in separate fields). I want to run a query that
returns the first character of each word in that field
(aka Initials). So if the text in the field is "Kristina
Smith", it should come back with "KS".

I can get the first initial, but not the second. So far
the SQL I have is:

Initials: Mid([Name],1,1) & Mid(Right([Name],InStr
([Name]," ")-2),1,1)

Maybe there's a different function I should be using?

Any assistance is much appreciated.
 
Kristina,

Initials: Mid([Name],1,1) & Mid([Name], InStr([Name]," ")+1, 1)

or

Initials: Mid([Name],1,1) & Mid([Name],InStrRev([Name]," ")+1,1)

As regards splitting the fullname field into two fields, I strongly
recommend that you do it. As time goes by, you will likely receive
additional requests for special formatting requiring that you split the
fullname field in some fashion more difficult than the current one. And
then, there is the variability of names. If you do not yet have names in
your database like:

Mary Jo Smith
John Paul van Meter

you will one day. It is definitely easier to concatenate than to split.
 
It worked! Thank you very much!
-----Original Message-----
Kristina,

Initials: Mid([Name],1,1) & Mid([Name], InStr([Name]," ") +1, 1)

or

Initials: Mid([Name],1,1) & Mid([Name],InStrRev ([Name]," ")+1,1)

As regards splitting the fullname field into two fields, I strongly
recommend that you do it. As time goes by, you will likely receive
additional requests for special formatting requiring that you split the
fullname field in some fashion more difficult than the current one. And
then, there is the variability of names. If you do not yet have names in
your database like:

Mary Jo Smith
John Paul van Meter

you will one day. It is definitely easier to concatenate than to split.



--

Cheryl Fischer, MVP Microsoft Access



I have a field that houses a first and last name (I don't
want them in separate fields). I want to run a query that
returns the first character of each word in that field
(aka Initials). So if the text in the field is "Kristina
Smith", it should come back with "KS".

I can get the first initial, but not the second. So far
the SQL I have is:

Initials: Mid([Name],1,1) & Mid(Right([Name],InStr
([Name]," ")-2),1,1)

Maybe there's a different function I should be using?

Any assistance is much appreciated.
Click to expand...


.
Click to expand...
 
Back
Top