NAMES
Figure 1 shows a data entry form to enter contact information. It’s based on a table called “Contact.” The labels used are generic so that people and entities of all kinds can be tracked in the same table. That’s also why the parts of a name are broken down into different parts and stored discretely in separate fields, such as MainName, NameA, and NameB. Combining (i.e., concatenating) data is a lot easier than separating or parsing it.
MainName is either the name of a company or organization or the last name of a human. NameA is the first name of a human, or it could be used for a subsidiary name for a company. NameB might be a person’s middle name. Title stores whatever gets used when formally writing the individual’s full name, such as Mrs., Mr., Dr., or Prof. Suffix is something like Jr., Sr., or III. Nickname is the name used in casual conversation.
At the top of the form’s detail section, the contact’s full name is displayed on an orange background. The expression to calculate this is MainName & IIf( IsNull(NameA) And IsNull(NameB), "", "," & (" " + Title) & (" " + NameA) & (" " + NameB) & (" """ + Nickname + """") & (", " + Sufx)). First, it displays the main name. The IIf tests the values of NameA and NameB to see if they contain data. And if they do, more text is displayed. If they are null, then a zero-length string is written and nothing else is tested—only the MainName value is displayed.
If other names are specified, a comma is added after the main name. Then, if there is a Title, a space and its value are displayed. The same is done for NameA and NameB. If there’s a Nickname, a space is added and then the nickname is displayed within quotation marks. Finally, if there’s a Suffix, then a comma, a space, and its value are displayed.
When string expressions are concatenated with an ampersand (&), nulls are treated as zero-length strings. In this way, if NameB is null, for instance, it has no impact on whether and how NameA or Nickname are displayed.
Alternately, if a plus sign (+) is used to concatenate string expressions and any part of it is null, then everything in the expression is null. Parentheses are used to group expressions so that the plus sign only applies to what’s inside the parentheses. For example, consider the nickname portion of the expression: (" """ + Nickname + """"). If there’s no nickname, then that whole part of the expression, including the spaces and quotation marks, is skipped.
OTHER DATA
Another example of information particular to a human contact is date of birth (DOB). Perhaps you know that the person’s birthday is December 5, but you don’t know the year. By collecting the date in separate parts (year, month, day), you only have to include however much you know. If it was captured all in one field, then you wouldn’t be able to add December 5 without including a year.
If all parts of a date are filled, Access can combine that information into an actual date using the DateSerial function, which requires numbers for year, month, and day. And once a date is known, the day of the week, such as Monday or Wednesday, can be known, too, which is nice.
The form also contains a combo box to specify category. This is a helpful way to organize the contacts into groups, such as colleague, friend, family, supplier, distributor, relative, service, club, manufacturer, or interest. The list comes from a “Category” table that contains the various category values you want to use.
Another combo box on the form is labeled “Head Contact.” In the combo box, you can specify another record that is the head of the current record. For example, if you are in the record of a company’s employee, you can use the combo box to select the record for the company, creating a relation between the two records. But it doesn’t have to be a company. It could be a club, the head of a household, or any other hierarchical relationship that bears the most importance with this contact.
There are several links on the form to other related information, such as addresses, phones, email addresses, websites, lists, and memberships. These are actually command buttons formatted to look like links. When you click on one, it will open other forms related to that particular piece of data. There also are command buttons to close the form or go to other contact records, and ways to find and filter data. These will be discussed in later articles.
Download this month’s database: SF1811_Contact-Database.
SF SAYS
Keep track of the important people and organizations in your life with a database for contact information.
November 2018
