Trimming names can be done in different ways. When dealing with large
numbers of records in a "Persons Table", it's often useful to create
customized name fields to supplement first name (FName) , middle name
(MName), last name (LName). Sometimes users need to work with additional
fields that make up a name set, such as prefix (aka honorary or title), and
degrees (aka suffixes). Fund raising and Conference Planning databases often
include name sets such as
FullName - FName, MName, LName
FormalName - Prefix, FName, MName, LName, Degrees
BadgeName (Name badges for conference planning) - FName, MName, LName,
Degrees (editable for variations)
AddressName1 - FName, MName of principal and FName, MName, LName of both
principal and spouse
AddressName2 - FullName of principal and FullName of spouse/partner (if last
names are different)
Usually these name set fields can be edited for customization without
changing the original FName, MName, LName values. I usually make the field
size 100, particularly for BadgName, FormalName, and AddressName
Name set fields can be created in different ways. (1) Working from existing
tables, particularly large ones, an update query is a fast ways of creating
the values you want in the name set fields. (2) If name sets need to be
created "on the fly" in a report, for example, a simple or complex expression
in a text field can work extremely well. (3) Event procedures can be created
for each of the name fields, including prefix, and degrees, from the
"AfterUpdate" line on the property sheet in form design view.
If you create a FullName field, you can use it to simplify expressions where
you need to add a prefix or suffix. For example, the FullName will trim the
absence of a middle name, leaving no spaces between first name and last name.
Using the FullName to add degrees allows you to ignore the absence of a
middle name and focus on eliminating spaces for those names where a degree is
absent, and a comma is not desired in the name set between last name and
degrees.
In your particular case, you apparently do not need or use a middle name
field or a degree/suffix field. In some ways this simplifies the expressions
needed to create your name sets. However, I find it important to have a
middle name field (for either a full middle name or initial) in order to find
duplicate records or at least identify a seemingly (but not true) duplicate
record. A very useful query can be created to find these records where the
first name, last name, and city are the same. This is particularly helpful
as a first/basic step (but not a comprehensive solution) when dealing with
thousands of "person" records -- association membership lists, mailing lists,
etc. It does not, of course, catch misspellings. I usually combine this
query with a full form view of the entrie name and address (two seemingly
duplicates to a page) so I can visually review enough information to
determine if the two records are true duplicates. Sometimes, same phone
numbers, street, or email addresses are determining factors.
Here are some examples:
MAILING LABELS or NAME/ADDRESSS LISTS. An expression in a text field on the
report that meets your requirements for a name set consisting of Prefix
("title"), FName, LName (no MName):
=Trim(IIf(IsNull([Prefix]) And IsNull([FName]),[LName],IIf(Not
IsNull([Prefix]) And IsNull([FName]),[Prefix] & " " & [LName],IIf(Not
IsNull([Prefix]) And Not IsNull([FName]),[Prefix] & " " & [FName] & " " &
[LName]))))
UPDATE QUERY. An expression, slightly different than above, to globally
modify the "PrefixName" (Name I give to meet your needs of a name set
consisting of Prefix ("title"), FName, LName (no MName):
IIf(IsNull([Prefix]) And IsNull([FName]),[LName],IIf(IsNull([Prefix]) And
Not IsNull([FName]) And Not IsNull([LName]),[FName] & " " & [LName],IIf(Not
IsNull([Prefix]) And IsNull([FName]),[Prefix] & " " & [LName],IIf(Not
IsNull([Prefix]) And Not IsNull([FName]),[Prefix] & " " & [FName] & " " &
[LName]))))
EVENT PROCEDURE: Place this procedure for each field. Click on field in
design view with Property Sheet open and click on "Event Procedure" on the
AfterUpdate line. Fields to enter the Event Procedure, of course, for your
requirements are (1) Prefix ("title"); (2) FName; (3) LName. This assumes
you do not use a Middle name field in your name set (based on your comments
in your question).
Example for first name field (FName):
----------------------------------------------------------------------------
Private Sub FName_AfterUpdate()
If IsNull(PrefixName) Or Not IsNull(PrefixName) And _
IsNull([Prefix]) And IsNull([FName]) Then
PrefixName = ([LName])
ElseIf IsNull(PrefixName) Or Not IsNull(PrefixName) And _
IsNull([Prefix]) And IsNull([LName]) Then
PrefixName = ([FName])
ElseIf IsNull([PrefixName]) Or Not IsNull([PrefixName]) And _
IsNull([FName]) Then
PrefixName = ([Prefix] & " " & [LName])
ElseIf IsNull(PrefixName) Or Not IsNull(PrefixName) And _
IsNull(LName) Then
PrefixName = ([Prefix] & " " & [FName])
ElseIf IsNull(Prefix) Then
PrefixName = ([FName] & " " & [LName])
ElseIf IsNull(Prefix) Or Not IsNull(Prefix) Then
PrefixName = ([Prefix] & " " & [FName] & " " & [LName])
End If
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
--------------------------------------------------------------------------------------------
With these event procedures, you will be creating the "PrefixName" field
"on-the-fly" as the data entry operator keys in the various values (names) in
the appropriate (field) controls on the data entry form.
Hope this helps.
