Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Combining Fields

Combining fields is easy enough BUT when there’s a chance one of the fields might be blank (empty), well, that presents an issue.  Especially, when you don’t want the comma, period or dash to show if the adjoining field is blank (empty), ie: Jane . Doe (no Middle Inital so only the period shows) or 9999- (no Order Line Number so only the dash shows).

Below are examples of how to combine the fields taking into account the adjoining field might be empty leaving off the perios, comma or dash (or any other symbol you might be using).

1.  Jane A. Doe and if no middle initial Jane Doe use…

[cpFirstName] & (" " + [cpMiddleInitial] + ".") & (" " + [cpLastName])

2.  Doe, Jane A.; and if no Middle Initial, Doe, Jane; and if no Last Name Jane, A. use…

([cpLastName] + ", ")  & [cpFirstName] & (" " + [cpMiddleInitial] + ".")

3.   Doe, Jane and leaving either First Name or Last Name blank will not result in an empty space before or after the name using…

([cpLastName] + ", ") & [cpFirstName] OR [cpLastName] & (", " + [cpFirstName])

4.  Doe Jane and leaving either First Name or Last Name blank will not result in an empty space before or after the name using…

([cpLastName]+" ") & [cpFirstName]

5.  Jane Doe and leaving either First Name or Last Name blank will not result in an empty space before or after the name using…

[cpFirstName] & (" "+[cpLastName])

6.  9999-1 and if there is no Order Line Number 9999 use…

[odOrderID] & ("-"+[odOrderLineNumber])

197 total views, 1 views today

Comments are closed.