ACCESS: BREAKING UP TEXT

TEST DATABASE

EMPLOYEE TABLE

To demonstrate this issue, I added an Employee table to our project database and populated it with test data. I created the table using a template available in Access that includes fields for employees’ names, phone numbers, addresses, and business information such as job title. I also added a Full Name field limited to 100 characters to use for our work.  

I entered data for four records. I only included data relevant to the examples here, so there are a lot of blank fields that can be used in the future. You may want to create your own “testing” database as you continue to learn how to use Access.  

CONCATENATE

Many data sources store name data in one field in a Last Name, First Name field, such as “Doe, Jane,” or “Smith, John.” So before we can practice breaking up the text, we first need to combine the data in the First Name and Last Name fields into the Full Name field.  

Create a query with the Employee table as a data source. Change the query type to Update. Drag the Full Name field into the first column of the query by design grid. In the Update To line, enter [Last Name]&”, “&[First Name], as shown in Figure 1. Run the query and save it as “Update Full Name.” Close the query.  

Figure 1  

The Full Name field of the Employee table now has data in it in a Last Name, First Name format. Open the table and delete the data from both the First Name and Last Name fields. Now our test database is ready.  

TEXT FUNCTIONS

MID AND INSTR

To separate the Full Name field into First Name and Last Name, we can use the MID function. The MID function in Access can be used to return a specified part of a string. It has three arguments: MID(text to search, start position within the text, number of characters to capture).  

In our example, the text to search is the Full Name field. Where the last name ends and the first name begins will vary with each record, however, so we can’t use the MID function on its own. What won’t change, however, is that each string includes a comma and a space separating the first and last names. That’s where the INSTR function comes in.  

INSTR locates a string within a string. It also has three arguments: INSTR(starting position, the string that will be searched, the string to search for). If you’re starting from the beginning of a string, the first argument can be either 1 or left out. For our purposes, we can use INSTR to find the comma within each Full Name string and combine it with a MID function.  

Create a query using the Employee table as a data source. Change the query type to Update. Drag First Name into the first column of the query design grid and Last Name into the second column.  

In the Update To line for the First Name field, enter MID([Full Name],INSTR([Full Name],”,”)+2). This will find the location of the comma and start the MID function after the subsequent space. In this instance, the INSTR is used as the second argument of the MID function, identifying where it should start counting. We want to capture the entire remaining portion of the string, so the MID function doesn’t include a third argument.  

For the Last Name field, type MID([Full Name],1,INSTR([Full Name],”,”)-1) into the Update To line. This will start the MID function at the beginning of the Full Name field and end it one character before the comma. Save the query as “Update First and Last Name from Full Name” (see Figure 2). Run the query and close it. Open the Employee table to make sure the updates were accurate. Adjust the query if needed.  

Figure 2  

LEN, LEFT, AND RIGHT

Other text-related functions are LEN, LEFT, and RIGHT. The LEN function will help you find the length of a string if you need to use that in your text manipulation. The LEFT and RIGHT functions are similar to MID. The LEFT function works just as you’d expect—it identifies a string of text beginning with the left-most character. The RIGHT function does the same starting from the right. Since they have fixed starting points, they both have only two arguments: (text to search, number of characters to capture).  

Both LEFT and RIGHT can be used effectively with INSTR. For example, we didn’t have to use MID functions. We could have used INSTR as an argument in a LEFT function to update the Last Name field and in a RIGHT function to update the First Name field. In both cases, the appropriate INSTR function would have told the two functions when to stop counting.  

Download this month’s database here: SF_MAY_2015.   

SF BEST PRACTICES