O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Returning the Start Position of a Substring When the Characters Are Known

problem

My data contains a sequence of characters embedded in a larger text string. The sequence of characters is always the same, but the starting position of the characters differs between records. What is an easy way to determine the starting position in each record?

Solution

The InStr function is useful in this situation. It determines the starting position of a string within a larger string. Since the string being sought is already known, determining its length is not an issue.

Figure 5-1 shows a table with records. Within each record is the substring 7XR3G. The positioning of this substring is not consistent across records.

Each record contains the same substring

Figure 5-1. Each record contains the same substring

You can use a query to determine the substring's start position in each record. You'll need to build a temporary field as an expression with the InStr function, as shown in Figure 5-2.

Using InStr in a query

Figure 5-2. Using InStr in a query

Figure 5-3 shows the result of running the query.

Discussion

InStr returns the starting position for a given substring. But what if the substring occurs more than once in the larger string? You may need to be able to determine where each occurrence begins, not just the first occurrence.

A little VBA code helps out in this situation. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required