Use Parsing To Cleaup Your Access Data

An MS Access Basic Parsing Routine

Here's a solution to a very common problem: Data coming into your database is not in a useable format for processing because it is part of a longer combination of text and/or numbers. This long "string" of text must be separated prior to entering into the database. When this occurs, you will need to establish a routine to manipulate the data into proper format.

NOTE: The following code is for demonstration purposes only and is not a complete coding solution.

The process of separating strings of data into useable data format is called "Parsing." The following is an example of a parsing routine to extract Part Numbers and Part Names. For the purposes of demonstration, we'll use list boxes to show the incoming data and the finalized or parsed data.

Access Table With Combination Data String

The Part Number and Part Name are joined and must be be separated for proper data functionality.

Parsing Form

The left List Box displays Part ID and the combined data string of Part Number and Part Name.

Parsing Form Showing Parsed Data In Right List Box

Clicking on the Parse Parts button runs code to populate the right side list box with individual Part Number and corresponding Part Name.

Solution Code:

cmdParse_Click

This sub routine runs when the Parse Parts button click event occurs. This sub routine declares a string variable, then populates the variable with a SQL select statement. Within the SQL statement are "calls" to the Parse1 and the Parse2 functions. These functions perform the actual data parsing. Once the parsing is completed, the row source for the right list box is assigned the value of the string SQL statement.

Parse1 Function

The Parse1 function routine receives a variable from the cmdParse_Click sub routine that represents the string of text to be parsed. The function declares an integer variable, then assigns the variable a value equal to the number of the location of the dash/minus sign within the string using the Access built in function "InStr". Next, the routine checks to make sure the value of the position is greater than 0 and assigns Parse1 the value of the string to the left of dash/minus sign. This is the Part Number. The Parse1 function returns the value to the calling routine, cmdParse_Click.

Parse2 Function

The Parse2 function routine receives a variable from the cmdParse_Click sub routine that represents the string of text to be parsed. The function declares an integer variable, then assigns the variable a value equal to the number of the location of the dash/minus sign within the string using the Access built in function "InStr". Next the routine checks to make sure the value of the position is greater than 0 and assigns Parse2 the value of the string starting immediately after the dash/minus sign, by using the Access built in "Mid" function. This value is the Part Name. The Parse2 function returns the value to the calling routine, cmdParse_Click.

A Closer Look At The Parsing Functions

iPosition = InStr(pValue, "-")
This returns the numeric value 5, meaning the dash/minus sign is located at position number 5 within the string.

If iPosition > 0 Then
The value of the variable is 5 so it is greater than 0.

Parse1 Function

Parse1 = Left(pValue, iPosition - 1)
The function Parse1 is assigned the value of all the text starting at the leftmost position and through the 4th position (5 -1 = 4) within the string variable pValue . So, 1111-Part1 returns 1111.

Definition:
The Left Function in Microsoft Access returns a substring from a string, beginning at the leftmost character. The character can be a letter, a number, or special character.

Parse2 Function

Parse2 = Mid(pValue, iPosition + 1)
The function Parse1 is assigned the value of all the text starting at the position immediately following the dash/minus sign (5 +1 = 6) within the string variable pValue . So, 1111-Part1 returns Part1.

Definition:
The Mid function in Microsoft Access returns a substring from a string beginning at any position within the string.

Conclusion:

This article demonstrates the basic principals associated with parsing strings of data in Microsoft Access using VBA (Visual Basic for Applications).