Returning Initials from Name field

[lscheer]

I am using the following expression to give me the first initial of the first and last names (concatenated into the "Name" field):

Initials: Left([Name],1)+Mid([Name],InStr([Name],""),2)


Does anyone have any ideas on how I might be able to accommodate for hyphenated surnames (such as: John Boxer-Smith) to return J B S as the initials?

Thanks!

 

[wazz]

Left("John Boxer-Smith",1) & Mid("John Boxer-Smith",InStr(1,"John Boxer-Smith"," ")+1,1) & _
Mid("John Boxer-Smith",InStr(1,"John Boxer-Smith","-")+1,1)

 

[lscheer]

Thanks wazz; this works great, except I am hoping to be able to accommodate both hyphenated and non-hyphenated names (i.e. John Boxer-Smith and Jane Jameson to yield "JBS" and "JJ" respectively). Would it help to run the expression on the First and Last Name fields separately and then concatenate them?

 

[wazz]

definitely.
you could also create variables for each part, or subpart. create an int variable that looks for the position of a hyphen (InStr(1,"John Boxer-Smith","-")). if it returns 0, there's no hyphen and you would know not to include/bother with that.

 

[lscheer]

Just for follow up in case anyone else is looking for this sort of info...

Here's what I used instead where FName is the First Name field and LName is the Last Name field. Then, in the query I concatenated the FInit and LInit fields to give me one "Initials" field for my form.

FInit: (Left([FName],1))

LInit: IIf(([LName] Like "*-*"),(Mid([LName],InStr(1,[LName]," ")+1,1) & (Mid([Name],InStr(1,[Name],"-")+1,1))),(Left([LName],1)))