On reviewing my log files I see that there are quite a few people coming in from various search engines looking for a way to split a name field into a first name and last name using Access. I know myself that this is a common issue, as many times this wasn't given much forethought when the table was originally created. Unfortunately, there is no really simple way to do this, at least that I've ever found. Fortunately, it can be done, albeit with usually less than perfect results. If you haven't ventured here before, consider this a welcome to the world of VBA (Visual Basic for Applications).

As a side note, it is considerably easier to assemble data than it is to split it apart. Therefore, when you're creating the fields in your database it is almost always better to break the fields down into smaller individual pieces (such as street, city, state, postal, country) instead of using one large field (address). Of course the tradeoff for this is that you must then reference those fields in any forms, queries, reports, etc. that you build on top of the table. You must balance the ability to manipulate the data by these individual fields against the difficulty incurred with creating them. Creating a database is as much an art as a science, it is very seldom that you will find a single 'correct' solution.

I have provided a sample comma-separated text file that you can use for this example if you want (you can download it here, right click, save file or target as). You should be able it import this fairly easily into your version of access. Save the file to your hard drive, create a new Access database, then go to File -> Get external data -> Import, and select Text files from the Type drop-down list. Once there you should be able to walk through the wizard without too much trouble, and import the data into a table called author. Make sure you select the box 'First Row Contains Fields Names' in the second dialog. The file is a list of authors pulled from Amazon.com that I was using in another project, and has enough records in it (11,000+) to give you a pretty good representation of the difficulties you may run into when attempting this.

The following example is meant as a fast, down and dirty way to split a field into a couple of values. If I were creating this for a production database I would write a more detailed function for this that would take some of the other variables into account, an example of which I will look to create for this site at a later date. This is meant as a simple example, not a comprehensive solution.

The first thing you need to do is to take a look at some of the sample data and try to put together a little game plan on how to proceed. Here is a small subset of the data in the file:

As you look at the data the question that comes to mind is: do I try to separate the first name or the last name? In a perfect world you would have a single full first name and a single full last name. Obviously, we don't live in a perfect world. Neither of those will be a perfect solution, so you try to do what will get the highest percentage of correct answers. In this case, we're going to split off the last full word in the name.

If we look at the data we see that there is always at least one space separating that name from the rest of the field. Therefore, if we test for the location of the last space in the string we should be able to split off anything that comes after it without too much trouble. The first thing we need to do is to find the location of that space character, then we need to separate anything that comes after it into the string that we're looking for.

We're going to need to use two built-in Access functions to do this:

• InstrRev(stringcheck, stringmatch[, start[, compare]]) - returns the position of an occurrence of one string within another, from the end of string.
• stringcheck - string being searched
• stringmatch - string that we're looking for
• start and compare are optional arguments that we're going to ignore in this example
• Mid(string, start[, length]) - returns a string containing a specified number of characters from a string.
• string - string to work with
• start - where to start
• length is an optional argument that we're going to ignore in this example

So InstrRev will tell us where the last space character in the data string is located, and Mid will allow us to split off anything that comes after that location. Let's take one at a time:

• We're going to be entering this directly into the default select query grid provided by Access, so we'll set it up for that. The string that we want to search for is going to be in the field ([author_name]), and the string we're going to be looking for is a space (" "). This will give us the position of the final space in the field, and we'll set it up like this:

InstrRev([author_name]," ")

• This gives us a number that corresponds to the location of that final space. What we're really looking for is the next character, so we'll need to add 1 to that value:

InstrRev([author_name]," ") + 1

• This has the added benefit of taking taking care of the error message we would receive in the case where there are no spaces in the field, as the full function below will use a 1 instead of a null value.
• Now we're going to use that value in the next function. So we know where this final space is, we can now say 'give us everything after that space'. We'll set it up like this:

Mid([author_name],InstrRev([author_name]," ") + 1)

• The final item to do is to create a name for the field in the query. You do this by entering the name you want to use, then a colon, then the function:

last_name:Mid([author_name],InstrRev([author_name]," ") + 1)

Now that we've got the function, all we have to do is to place it in the query grid. I'm going to assume that you've imported the data from the file mentioned above into a table called 'author'. If you've had a problem doing that then most likely so have a whole bunch of other people, so leave a comment and I'll try to address it. We need to create a new query in design mode:

Which will give us a blank query, looking at the design grid and ready to add a table. Add the author table like so:

Now double-click on the field author_name, or drag and drop it from the table down to the grid:

So all we need to do now is to type the function in to another field on the query grid. I'll give you a tip here, it can be difficult to enter a relatively long function like this into the Field: text box. Right click on the text area and select Zoom:

to open up a larger text dialog, into which you can type the function we just created:

Click OK, then select Datasheet view from the main toolbar:

To give you the results:

As you scroll down through the table you will see some problems with the solution. It isn't perfect, but it is a start. If you're feeling adventurous you can try creating another function that will pull the first part of the field. Give it a shot, you might be surprised at what you can do.

Bookmark/Search this post with:

delicious | digg | reddit | newsvine | technorati