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.
|Printer friendly Cite/link Email Feedback|
|Title Annotation:||TEXT FUNCTIONS|
|Date:||May 1, 2015|
|Next Article:||Len, left, and right.|