How to split a field into parts

[doyle60]

I posted the below question this weekend to the group and got no
answer. I would appreciate the most complete answer possible but I can
probably figure it out myself if I knew what functions to use. Can
someone list the functions with a brief discription? Thanks.
________________________

I have a friend who built a wonderful database but mistakenly put too
much information in one field. For a table of People, he put into one
field the following:


George Mitchell (b. 1976), husband of Mrs. Mitchell (1978-1999)
Hank Natty Barnes (1899-1901), brother of so and so.
Nelly Margeret Stevens (1978-1999) actress
Telsea "Minnie" Mack-Evens (b. 1878)


It has about a thousand records with data similar to the above.


I want to know how to split the above, using a query, into five fields.
Here are the fields and the rules:


FirstName:
If there are three or more words before the first peren, put the first
two words, if two or one word, put the first word only. (Best to treat
hypenations as one word but this can be hand corrected later).


LastName:
Put the word before the first peren.


BirthYear:
Some cells have two sets of perens, so only for the first set if it
takes this form (b. 9999), put 9999, or if it takes this form
(9999-9999), then put 9999 (the birth year, of course).


DeathYear:
If the cell has (9999-9999) then give me 9999 (the last year). No
cells have (d. 9999).


Comment:
All the field's comment after the second peren, beginning with the
first letter.


Even after this, much hand correcting, though a thousand records will
be done.

Thanks for your help,


Matt

 

[Marshall Barton]

I posted the below question this weekend to the group and got no
answer. I would appreciate the most complete answer possible but I can
probably figure it out myself if I knew what functions to use. Can
someone list the functions with a brief discription? Thanks.
________________________

I have a friend who built a wonderful database but mistakenly put too
much information in one field. For a table of People, he put into one
field the following:


George Mitchell (b. 1976), husband of Mrs. Mitchell (1978-1999)
Hank Natty Barnes (1899-1901), brother of so and so.
Nelly Margeret Stevens (1978-1999) actress
Telsea "Minnie" Mack-Evens (b. 1878)


It has about a thousand records with data similar to the above.


I want to know how to split the above, using a query, into five fields.
Here are the fields and the rules:


FirstName:
If there are three or more words before the first peren, put the first
two words, if two or one word, put the first word only. (Best to treat
hypenations as one word but this can be hand corrected later).


LastName:
Put the word before the first peren.


BirthYear:
Some cells have two sets of perens, so only for the first set if it
takes this form (b. 9999), put 9999, or if it takes this form
(9999-9999), then put 9999 (the birth year, of course).


DeathYear:
If the cell has (9999-9999) then give me 9999 (the last year). No
cells have (d. 9999).


Comment:
All the field's comment after the second peren, beginning with the
first letter.


Even after this, much hand correcting, though a thousand records will
be done.

InStr - can be used to find the first or next occurance of a
specific substring (or character) within a string.

Mid = can be used to extract a part of the string.

Len - can be used to determine the length of a string.

Left and Right may be more convenient than Mid in some
cases.

For example:

lngDateStart = InStr(thefield, " (")
If lngDateStart > 0 Then
'the date part was not found
Else
lngDateEnd = InStr(lngDateStart, thefield, ")")
strDatePart = Mid(lngDateStart,lngDateStart + 2, _
lngDateEnd - lngDateStart)
strComment = Mid(thefield, lngDateEnd +1)
. . .
End If

 

[doyle60]

Thanks. Let me do one at a time. I have code that gives me the names
before the first perenthesis:

NameAdj2: Left([Name],InStr([Name]," (")-1)

So this:

George Mitchell (b. 1976), husband of Mrs. Mitchell (1978-1999)
Hank Natty Barnes (1899-1901), brother of so and so.
Nelly Margeret Stevens (1978-1999) actress
Telsea "Minnie" Mack-Evens (b. 1878)

Becomes this:

George Mitchell
Hank Natty Barnes
Nelly Margeret Stevens
Telsea "Minnie" Mack-Evens

But now I need to split it up into FirstName and LastName. Is there a
function that can tell me how many spaces are in the string? If so, I
could use that. But I'd also need code that tells me the position of
the last space, or the second space. What function does that? I can't
quite figure out how InStr does that, if it does.

Or is there a better way altogether?

By the way, I'm doing this in a query.

Thanks,

Matt

 

[Marshall Barton]

Thanks. Let me do one at a time. I have code that gives me the names
before the first perenthesis:

NameAdj2: Left([Name],InStr([Name]," (")-1)

So this:

George Mitchell (b. 1976), husband of Mrs. Mitchell (1978-1999)
Hank Natty Barnes (1899-1901), brother of so and so.
Nelly Margeret Stevens (1978-1999) actress
Telsea "Minnie" Mack-Evens (b. 1878)

Becomes this:

George Mitchell
Hank Natty Barnes
Nelly Margeret Stevens
Telsea "Minnie" Mack-Evens

But now I need to split it up into FirstName and LastName. Is there a
function that can tell me how many spaces are in the string? If so, I
could use that. But I'd also need code that tells me the position of
the last space, or the second space. What function does that? I can't
quite figure out how InStr does that, if it does.

InStrRev would be far easier to use to find the last space
in the name substring.

 

[doyle60]

Thanks. But InStrRev is not a defined function in my Access 1997. I'm
trying to use it in a query, if that matters.

I still need a function that tells me how many spaces are in a string?
But if I get the InStrRev to work, I could just say: if InStr is equal
to InStrRev, then this, and if not, that. Now I'm thinking. Thanks,

Matt

 

[Brendan Reynolds]

InStrRev was new in Access 2000. The best way to count the number of spaces
(or any other character) in a string uses Replace, but Replace was also new
in Access 2000. Here's an alternative, won't be as fast, but will work in
Access 97 ...

Public Function HowManyChars(ByVal TheString As String, ByVal TheChar As
String) As Long

Dim lngLoop As Long
Dim lngCount As Long

For lngLoop = 1 To Len(TheString)
If Mid$(TheString, lngLoop, 1) = TheChar Then
lngCount = lngCount + 1
End If
Next lngLoop

HowManyChars = lngCount

End Function

 

[doyle60]

Thanks. I do have Access 2000 at home and the InStrRev is certainly
not a function in it. Your HowManyChars works but I'm afraid I still
need the position of the last Space in the string to split the name in
half properly.

Here are some sample names:

George Mitchell
Hank Natty Barnes
Nelly Margeret Stevens
Telsea "Minnie" Mack-Evens
Kent George Matty Gilbert Robertson

I need to split them like this (I'll hand fix compound sir names):

George Mitchell
Hank Natty Barnes
Nelly Margeret Stevens
Telsea "Minnie" Mack-Evens
Kent George Matty Gilbert Robertson

I don't see how to do that without knowing the position of the last
space.

Basically I need a function that will help me put the last word in one
field and all preceeding words in another.

Thanks,

Matt

 

[Brendan Reynolds]

InStrRev *is* in Access 2000 - it was one of the new string functions that
were added to VBA 6, and Access 2000 was the first version to use VBA 6.
Some of those new functions could not be used directly in a query, though,
and I believe InStrRev was one of those. You'd have to write a custom VBA
function to call InStrRev, and then call the custom function from your
query.

In Access 97, you could do something like this ...

Public Function GetLastChar(ByVal TheString As String, ByVal TheChar As
String) As Long

Dim lngLoop As Long
Dim lngPos As Long

For lngLoop = Len(TheString) To 1 Step -1
If Mid$(TheString, lngLoop, 1) = TheChar Then
lngPos = lngLoop
Exit For
End If
Next lngLoop

GetLastChar = lngPos

End Function

? getlastchar("this is some text", " ")
13

 

[John Nurick]

InStrRev *is* in Access 2000 - it was one of the new string functions that
were added to VBA 6, and Access 2000 was the first version to use VBA 6.
Some of those new functions could not be used directly in a query, though,
and I believe InStrRev was one of those. You'd have to write a custom VBA
function to call InStrRev, and then call the custom function from your
query.

These functions seem to be properly available on Office 2000 installations
with recent service packs and/or Jet versions (I've never really used Office
2000 and don't know the details.)

 

[doyle60]

Thanks. This did it. I now have split the overloaded field into five
basic parts.

Yes, I was trying to use the InStrRev in a query in Access 2000.

Thanks so much,

Matt