Separate and uppercase (1 Viewer)

EddP · Jul 12, 2018

Hello
I have a table that is always being appended with new data on a weekly basis. The fields, as is, are entered are generated from another database so it is being uploaded into my database for formatting.
The field I need assistance with is a 'PERSON' name field.
Currently the names appended are formatted as (Last_Name, First_Name)
I would like to separate Last_Name from First_Name and then combine them back as (First_Name Last_Name), and the first letter of each name capitalized and the rest lower case. Is this possible?
SO I expect I would do this in a query so that the reports I generate are already formatted?

arnelgp · Jul 12, 2018

to separate the fullname, create a function in standard module and call it in your query:

Code:

select Person, fncPerson([Person], "FirstName") As FirstName, fncPerson([Person],"LastName") As LastName From Table1

your function:

Code:

Public Function fncPerson(pName As Variant, sPart As String) As Variant
Dim var As Variant
fncPerson=pName
If pName & ""=vbNullString Then Exit Function
var=Split(pName, ",")
If sPart="FirstName" And UBound(var) > 0 Then
fncPerson=Proper(var(1) & "")
End If
If sPart="LastName"  Then
fncPerson=Proper(var(0) & "")
End If
End Function

MajP · Jul 12, 2018

I believe Proper is a excel function. Does not work for me in access. I had to use strConv instead

Code:

Public Function CleanName(TheName As Variant) As String
  Dim firstName As String
  Dim lastName As String
  Dim aName() As String
  If Not IsNull(TheName) Then
    aName = Split(TheName, ",")
    firstName = Trim(aName(1))
    lastName = Trim(aName(0))
    firstName = StrConv(firstName, vbProperCase)
    lastName = StrConv(lastName, vbProperCase)
    CleanName = firstName & " " & lastName
  End If
End Function

arnelgp · Jul 12, 2018

Yes sorry.
Should be:

Public Function fncPerson(pName As Variant, sPart As String) As Variant
Dim var As Variant
fncPerson=pName
If pName & ""=vbNullString Then Exit Function
var=Split(pName, ",")
If sPart="FirstName" And UBound(var) > 0 Then
fncPerson=StrConv(var(1) & "", 3)
End If
If sPart="LastName" Then
fncPerson=StrConv(var(0) & "", 3)
End If
End Function

Separate and uppercase (1 Viewer)

EddP

New member

arnelgp

..forever waiting... waiting for jellybean!

MajP

You've got your good things, and you've got mine.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread