Change case in EmployeeName field

[krazykasper]

I need to change the EmployeeName field in my report (based on a query) to Proper case and change it from LAST, FIRST or LAST, MI, FIRST (Results should look like:John Q Smith).

The name styles in the source table vary as some are LAST, FIRST, MI; others are LAST, FIRST. Also, some are all caps and others are not. I do not have authority to change the field in the table (ODBC link).

Seems like it should be an easy fix but I haven't been able to figure it out. I can move the first name in front of the last name using TRIM but that's as far as I've gotten. Can anyone provide me some code that will get me started?

Thanks,

Krazy (Bill) Kasper

 

[CyberLynx]

Give this some thought (or something like it)....

Create a Text Box field in your Report and name it FullName. Set the Control Source to FullName. Enter the Following SQL Statement into the Record Source property of the Report Form:

SELECT UCase(Left(FirstName,1)) & Mid(FirstName,2) & " " & IIf(MddleName IS NOT NULL,UCase(Left(MiddleName,1)) & Mid(MiddleName,2) & " ","") & UCase(Left(LastName,1)) & Mid(LastName,2) AS FullName, * FROM [[I][COLOR="Red"]YourTableName[/COLOR][/I]];

The above statement assumes of course that in table, the First Name field is named FirstName, the Middle Name field is named MiddleName, and the Last Name field is named LastName.

.

 

[DCrake]

A more consise approach would be to use the following syntax

Me.TxtFullName = StrConv(FirstName & " " & MiddleName & " " & LastName,3)

Again where First, Middle and Last are your filed names.

 

[wazz]

i think he's dealing with one field that holds the entire name or (partial names). (?)

 

[CyberLynx]

That certainly is more concise DCrake...Thank you.

SELECT StrConv(FirstName & " " & IIf(MiddleName Is Not Null,MiddleName & " ","") & LastName,3) AS EmployeeName, * FROM [[COLOR="Red"][I]YourTableName[/I][/COLOR]];

.

 

[krazykasper]

Thanks. I think that will do it.

KK