Access Report

[Prunes]

Hello Friends,
I need some help. Does anyone know how to convert a full name eg John Doe to J. Doe,. I am working on a report that needs to generate a letter with a Dear J.Doe but I don't know how to do this. The query is based on one table with one name field. This is MS Access 2003

Prunes

 

[dhookom]

Are all of your name fields used consistently in every one of your records? Are there any middle initials? How about first names that contain a space?

This is one of the reasons why first and last names should always be stored in more than a single field.

However to get "J. Doe" from "John Doe" the expression would be something like:

=Left([FullName],1) & ". " & Mid([FullName], Instr([FullName]," ")+1)

Duane
Hook'D on Access
MS Access MVP

 

[Prunes]

Thanks for your lightening speedy response. To answer your questions:
1.Are all of your name fields used consistently in every one of your records? = Yes
2.Are there any middle initials? = yes
3. How about first names that contain a space? = yes (Leigh Ann)

 

[dhookom]

You really can't answer yes to number 1 if either 2 or 3 are answered yes. It sounds like some names have middle initials and some not. You can't write code or expressions that would take a full name and understand what might be a two part first name vs a one part first name with a middle name.

There are some fairly good name parsing functions available.
Google It

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

=Left([FullName],1) & ". " & Mid([FullName], InStrRev([FullName]," ")+1)

Randy

 

[Prunes]

The name field has names that have middle initials as well as middle names and some do not have middle initial nor middle names. I found the database already made and I am still learning the application.

Here is a sample of how the names are listed in the table:

Goldstein, Leigh Ann
Gacina, Marleena
O'ndiek, Paulo S.

 

[Prunes]

Now the problem I have is that the names were listed LastName, FirstName like in the example I gave above. How do I switch the names around? Example:

This is what I am getting with the code
Goldstein, Leigh Ann ===> G. Leigh Ann
Gacina, Marleena ===> G. Marleena
O'ndiek, Paulo S. ===> O.Paulo S.

What I would like is
Goldstein, Leigh Ann ===> LA. Goldstein
Gacina, Marleena ===> M. Garcina
O'ndiek, Paulo S. ===> P.S. O'ndiek

Is this possible?

Thank you soooo much

 

[lespaul]

ok since it's last name first with a comma you can do this (typed untested):

mid(FullName, inStr(FullName, ", ") + 1, 1) & "." & left(FullName, inStr(FullName, ",")

Leslie

Have you met Hardy Heron?

 

[randy700]

By making some minor modifications to the code dHookum and I have provided, you should be able to move the various parts of the name into separate fields. I'd create a new table, designed to put first, middle, and last names in separate fields, and move the data. You won't get all of them, but should get the majority. The problems you'll still run into will include such things as spaces in last names.


Randy

 

[dhookom]

Prunes,
You should really take a look at your data prior to posting a question here. It might be a good exercise to see how to handle all possible full name values. However, it is a bit frustrating trying to hit a moving target.


Duane
Hook'D on Access
MS Access MVP

 

[Prunes]

Sorry for the frustration. It's just that I am a real novice at Access. Just starting to learn the app.
I appreciate all the help. Will let you know if I get it working.