How to anonimise a client name?

[optionone]

How can I create an 'anonimised' field for a client's name (eg Joe Bloggs) to something like just the initials (say J B or JB)?

Need something like this excel formula but for access:
http://chandoo.org/wp/2008/09/02/get-initials-from-name-excel-formula/

Many thanks

 

[DCrake]

Yes it can be replicated using a simple function to look for spaces and taking the first letter after the space. Try searching for Split()

 

[optionone]

Hi DCrake,

Thanks for the info - does that function need to be used in vba ? I was hoping to use something that could go in to a select query (says undefined function when run)

Cant seem to find any google results either besides for people having trouble with the LEFT and INSTR functions as to middle names and hypenated names etc

 

[ghudson]

You will have problems is all parts of the name are in one field. Or, do you have a First, Middle and Last name fields for all three parts? If not, you should.

 

[optionone]

I dont unfortunately have the client name split out to first/middle/last as I am importing off data that is given to me :/

What about selecting capital letters? is that just as long winded and inaccurate?

 

[vbaInet]

Thought I should help since ghudson and DCrake aren't online at the moment.

Are you doing this on query level or on a control on a form?

 

[optionone]

query level - the query is then used as a source for a pivot table and the names need to be anonimised

 

[vbaInet]

Right, you need a function for this. Few mins.

 

[vbaInet]

Here you go:

Public Function GetInitials(fullName As String) As String
    Dim arraySplit$()
    
    GetInitials = ""
    arraySplit = Split(fullName, " ")
    For Each element In arraySplit
        GetInitials = GetInitials & UCase(Left(element, 1)) & " "
    Next
    GetInitials = Trim(GetInitials)
End Function

Put that query in a MODULE and call it from the query like this:

GetInitials([FullName_Field])

 

[optionone]

Thanks vbaInet - much appreciated

 

[vbaInet]

You're welcome

 

[rodmc]

Hi

sorry to resurrect this thread, but what if the surname is a Mc or Mac and you also want it to take the first initial after the Mc or Mac, in my case McEwen so that it would read R McE

 

[vbaInet]

The thing is there is more to it than just Mc or Mac. What about O'Brien and Radley-Smith?

 

[rodmc]

The thing is there is more to it than just Mc or Mac. What about O'Brien and Radley-Smith?

LMAO

cheers dude, think I might just tell them they're only getting 2 initials and they'll just have to like it

 

[vbaInet]

It can be coded. Just tell me what those two cases should be and I'll tinker with it.

 

[rodmc]

It can be coded. Just tell me what those two cases should be and I'll tinker with it.

Cases? do you mean like examples?

Dont want to bother you unnecessarily, its like you said where do you draw the line with the likes of Mc, Mac, O'Toole and the youve got people with dutch names and such like Van Der Whatever.

 

[vbaInet]

Ah yes, I forgot the Dutch, French and German names. Yes, I agree you should just give them the first letter because as time goes on you will need to refine the code if a new case was determined.

 

[rodmc]

Ah yes, I forgot the Dutch, French and German names. Yes, I agree you should just give them the first letter because as time goes on you will need to refine the code if a new case was determined.

sounds good to me!

thanks for responding anyway

 

[vbaInet]

No probs!