Parse FIRST / LAST name and change case structure. 1

[LD1010]

Thanks for reading my post.

A co-worker has asked me to help her take a mailing list that has been kept in an Excel workbook and convert it into an Access DB. Importing the data is no problem. The first problem is that the names, First and Last, were entered in one column. The second problem is that probably 80% of them were written in upper case. What I would like to end up with is a FirstName and LastName field and that the names start with an upper case letter followed by all lower case. I was able to parse out the first and last names in a query as follows: FirstName: Trim(Left([Name],InStr([Name]," ")-1)) LastName: Trim(Mid([Name],InStr([Name]," ")+1,1000)) but can anyone help with getting the case structure correct?

Thanks for any help.

 

[dhookom]

You can use the function StrConv([Your expression here],3) to convert a string to propercase. For instance:
StrConv("LD1010") = Ld1010
StrConv("ANDERSON",3) = Anderson
BTW: You don't need the 1000 in the mid function since the argument is optional. Try something like

LastName: StrConv(Trim(Mid([Name],InStr([Name]," ")+1)),3)

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Look at the StrConv function

? StrConv("lAStnAMe", 3)
LastName

The "3" produces a Proper Case result which capitalizes the first letter and makes the following letters lower case.

You can use

LastName: StrConv(Trim(Mid([Name],InStr([Name]," ")+1)),3)

If you omit the third argument for Mid then it defaults to the end of the string.

 

[LD1010]

Thanks for your help Duane, and for the many times you've helped in the past!! Works great.