In the products table of our sample database, there’s a serial number field that contains values like 180515-PLB-11398-1014. Each part of the serial number represents a distinct piece of information that should be in its own field. The first part is the manufacture date, but the table already has a field that stores that data. The second part of the serial number is the style, the third part is the factory-assigned number, and the fourth part is the product size.
With Split, we can isolate each individual part. Split separates a string into an array of multiple values that are indexed starting with 0. It’s only available in VBA, so you must write a function, which might also be called a user-defined function (UDF), to use it in a query.
VBA
To create a function, press Alt+F11 to open the VBE (Visual Basic Editor). Create a new module sheet by choosing Insert, Module from the menu. Type the lines shown in Figure 1.
Code is composed of statements, which might include declaring a new function or calculating and assigning a value to a variable. Interspersed among the code are comments, which are indicated by green text beginning with an apostrophe. These don’t impact the code and aren’t necessary for it to work. They’re added by programmers to describe what’s going on. This helps others who might use the code—or refresh the programmer’s memory at a later date.
The first statement in the code specifies that the procedure is a function named “GetNthPart” whose return data type is Variant. Three parameters are specified inside the parentheses. The first, pvString, is defined as a variant. This means it can be a string, number, date, or nothing (Null). The second parameter is piPart, which is an integer representing the part number you want to extract. The third parameter is the delimiter between the different parts. This parameter is optional. If no delimiter is specified, a dash will be used.
After some comments, the next statement in the code is “On Error Resume Next”—this means that errors will be skipped. Next, the return value is initialized to Null in case the function can’t be evaluated because data is missing or a parameter isn’t valid.
The statement beginning with “GetNthPart = Split” assigns the result of the expression using Split to GetNthPart, which makes it the function return value. Split divides pvString into parts delimited by the psDeli variable. If pvString isn’t text, it will be converted automatically to text.
Since Split returns an array but the function can only return one value, the desired element number, piPart, is specified in parentheses so that a single value is returned. One is subtracted from the part number sent, since the first array index is 0, not 1. The end of the function is denoted by “End Function.”
Once you’ve entered the code, go to the menu and choose Debug, Compile. If there are any problems, the first line with an error will be highlighted. If nothing happens, great! Save the module as “mod_Get_NthPart” since the name must be different than any procedure name.
ADD FIELDS
Before we use our function to split the serial number into separate parts, let’s first create a place to store the parts. Open the Productz table in Design view and create three fields to hold the extracted information: Style, Nbr, Siz. (Siz is spelled without an “e” because “Size” is a reserved word.) Here are the main properties for each:
- Style: Short Text; Description = Product Style; Size = 7.
- Nbr: Number; Description = Factory Number; Size = Long Integer; Default Value = Null.
- Siz: Short Text; Description = Product Size; Size = 5.
UPDATE QUERY
Now we need a new query to fill in those new fields with the proper data. Open a new query in Design view with the Productz table as its source. On the Design tab of the ribbon, click on Update to make it an Update query.
Add the three new Productz fields to the design grid: Style, Nbr, and Siz. In the Update To cell under each field, enter the expression to capture the appropriate part of the serial number. For Style, it’s GetNthPart([SerialNum],2). For Nbr, it’s GetNthPart([SerialNum],3). And for Siz, it’s GetNthPart([SerialNum],4). (See Figure 2.)
Save the query as “qUpdateProductz” and run it. You may get an error message that one field won’t be updated due to a type conversion failure. That’s okay. Click the Yes button to continue anyway. Open the Productz table to see the updated fields. If the results aren’t as expected, delete the new values and check the query to make sure it’s correct. Make any needed adjustments, and run it again. We’ll look at how to do that next month.
Download this month’s databases: SF1908_Update_Split
SF SAYS
To parse data, define a VBA function using Split.
August 2019
