Returning Initials Only

[Perfac]

Trying to learn how to do the following. If there is a field "NAMESONID"; the person may have one name or multiple names.
If the name is John. I would like to return a "J" in the field "INITIALS". That I can do.
If the person has multiple names, let us say John Ross Peter. I would like to return JPR in the Initials field.


The expression or VBA should therefore return the correct initials, no matter the amount of names.
Thank you.

[Welshgasman]

I would use the Split() function and then concatenate Left(ArrayName,1) within a loop of the split data.
UCase the data before returning it to the calling code.

Create a function to do all that.

[Perfac]

Thanks. Sorry, I need more spoon feeding. I never used split before. Just used an hour to search how to do it, not there yet. My field holding the names, maybe 1, maybe multiple is "NamesOnID". Using Split("NamesOnId") didnt work yet.

[CJ_London]

put this in a standard module

function getInitials(s as string) as string
dim a() as string
dim i as integer

    a=split(s," ")
    for i=0 to ubound(a)
        getInitials=getInitials & ucase(left(a(i),1)
    next i

end function

and call it in your query

Initials:getinitials([fullname])

note this code is aircode so check for typo's and you may need error handling in the event you try to pass a null or zls value for fullname

[Welshgasman]

https://docs.microsoft.com/en-us/off...split-function

[Perfac]

Thanks CJ. I created a standard Module, named Initials. In the query q01Employees I called Initials: getinitials([NamesOnId]). See the result on the image, opening the query again.

[orange]

Is missing a bracket??

Function getInitials(s As String) As String
Dim a() As String
Dim i As Integer

    a = Split(s, " ")
    For i = 0 To UBound(a)
        getInitials = getInitials & UCase(Left(a(i), 1))
    Next i

End Function

[Perfac]

Baie Dankie. Dit werk goed.

[orange]

Jy is welkom. Sterkte met jou projek

[CJ_London]

I did say you would need to check for typo's

[Perfac]

Pleasant surprise!!!

[Perfac]

Thanks CJ for solving my issue!