Field with both first name and last name

[THINKINGWAY]

I have a table where the field PRODUCTION_DESIGNER is composed of a persons
first and last name with a single space between them, for example "David
Henderson" is in the contents. Select this field and truncate this to the
initials of the first and last name and place into another field leaving the
original field untouched. The new field would have "DH" as its contents.

Thank you

 

[John Spencer]

Use the expression

Left(Production_Designer,1) &
Mid(Production_Designer,Instr(Production_Designer," ")+1,1)

If you want to do this in an update query

UPDATE [Your Table Name]
SET [Initials] = Left([Production_Designer],1) &
Mid([Production_Designer],Instr([Production_Designer]," ")+1,1)
WHERE Production_Designer is not null

NOTE: Backup your data first. That way if this fails to do what you want
you can set the data back to its original state and try something else.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dale Fye]

The only problem with John's solution is that it assumes that none of the
values in your [PRODUCTION_DESIGNER] field contain NULLs, and that every
record contains a space.

If you are absolutely sure that every record has a first and last name,
separated by a space, great. But if you are not absolutely sure, then you
might want to write a function that you can pass a variant (to handle NULL
values) and then perform some tests on it before you return a value.

Then, you could call this function:

SELECT ..., fnInitials([Production_Designer]) as Initials
FROM yourTable

Public Function fnInitials(Byval SomeName as Variant) as string

'Returns an empty string if SomeName is NULL or blank
'If there is no space in the name, it returns a single letter
'Otherwise, returns two characters
SomeName = TRIM(SomeName & "")
if LEN(SomeName) = 0 then
Initials = ""
elseif instr(SomeName, " ") = 0 then
Initials = Left(SomeName, 1)
elseif
Initials = Left(SomeName, 1) _
& Mid(SomeName, instr(SomeName, " ") + 1, 1)
endif

End Function

HTH
Dale