CAR Tech Tip
Splitting up isn't hard to do
By Richard Mullins NICAR
This column will go over the same ground as an earlier column - how to use string functions to split a field - but with the functions and commands used in Access instead of FoxPro.
If you work with databases for very long, you will face the question, "How do I split apart a field?"
If your search for help leaves you with a half-dozen offers of "I don't know Access. I like FoxPro better. I can show you how to do it FoxPro," then there's this month's column. It's not person-to-person, but you can tape it to your monitor.
Little difference
The differences between FoxPro and Access are not all that great, especially if you think about it right. You probably don't need anyone else telling you how to think, but that's OK because I might not do a very good job and so no harm will be done.
Here is one way to state what's needed to solve the problem: You need to understand the three FoxPro functions, left (), at (), and substr ()
Here is a better way to state what you need to know. You need to understand that all database programs have a way to look at strings and:
1) Pluck out a certain number of characters starting from the left, or from anywhere in the string.
2) Locate the position of a certain character.
To illustrate the solutions, I'll assume a table called Contribs with the original field called name, which contains a last name, then a comma, then the rest of the name: the first name, and perhaps middle name or initial. There are two new columns called last and rest.
The examples will be in SQL, which you can type into the SQL view of an Access query.
One of the benefits of SQL being the lingua franca of databases is you can pass along plain text of a query and not have to describe the specific screen of a specific database program. Last month's column presented the FoxPro/Access-SQL versions of updating columns and deleting rows.
Following the usual typographic convention, the SQL words are all caps. The string function words are in italic, a departure from the usual convention.
To split off the last name into the field called last.
UPDATE Contribs SET last = Left (name, InStr (name, ",")-1) WHERE (Len (name) >0 AND name IS NOT NULL AND InStr (name, ",") > 0 )
Now for the explanations: The function, instr (), which you can remember as being short for in-string, is the Access equivalent of the FoxPro function at (). These functions give the position of the comma within the field called name.
To avoid taking the comma trailing the name, you subtract 1 from the number that indicates the comma position.
Where conditions
In SQL update queries, all rows are changed unless you include a WHERE condition. In this case, the WHERE statement tells Access to split all the name fields apart, but only if three conditions are true: 1) the field is not empty, 2) the field is not NULL, and 3) the comma can be found in the field.
This is necessary in Access to avoid error messages that FoxPro would never trouble you with.
I know that conditions one and two seem to be saying the same thing, but in Access, which is more precise than FoxPro in the way it deals with nothing, a field can be NULL or it can contain a string whose length is zero.
To split off the rest of the name into the field called rest.
UPDATE Contribs SET rest = Mid (name, InStr (name, ",") +1) WHERE (Len (name) > 0 AND name IS NOT NULL AND InStr (name, ",") > 0 )
The Access function for going to any position within a string is mid. The FoxPro equivalent is substr().
Again, we have to find the comma, but not put it in the destination field. So for the right split, we add 1 to the position of the comma.
Richard Mullins can be reached at (573) 882-2127, or send e-mail to richard@nicar.org