Multiple delimeter split

[Leeman]

This is my first post here . I will try to remember all the sticky points i have just read for noobies. I am new to Access. I am using Access 2007.

Problem: I need some assistance in developing a module/procedure that will seperate a single column from a table based on different delimeters and numbers of delimeted text.

The table name is called "Allbookings" and looks like this:
Fullname
Smith/John C Mr
Citzen/Bob E W Mr
Abel/Jane Ms
..
..
Zanda/Andrew J

The result should look like this

Surname    FirstName    Other1    Other2    ..    OtherN    Salutation
Smith        John          C                                     Mr
Citzen       Bob           E         W                           Mr
Abel         Jane                                                Ms
..                        
..                        
Zanda       Andrew        J

The fields above are shown in bold and I would also like to keep the Fullname field.

The delimeters as shown above are "/" and " "

The salutations could be either Mr, Ms, Miss, Prof, Dr.

The table I am using has other columns and around 50,000 records.

Thank you in advance.
Leeman

 

[jal]

Run a query that replaces the slashmarks with a space (the result will be that you now only have one kind of delimiter). Then do the split. Question is, is every line guaranteed to have a salutation?

 

[Leeman]

Thanks.

The replace option sounds like a good idea except there are some surnames with space in them also.

A person may have or not have a salutation. the table is based on user entries and some dont include their salutation.

 

[jal]

Secondly, can you set a definite limit on the number of columns? Or does the number of columns need to be determined dynamically?

 

[jal]

You might want to have a list of all possible salutations so that the logic can detect them.

The replace option sounds like a good idea except there are some surnames with space in them also.

Not sure why that would be a problem. I am not replacing the spaces, just the slashes.

 

[Leeman]

I agree it will need to be done in two steps. First to split the surname using a "/" delimeter and placing this INTO a new column

The second step to seperate the other names using the " ". There would not be more than 5 columns for the names.

The Salutation is always at the end the name string.

The may be cases (errors) where there is no first name also. i.e Surname only

 

[Leeman]

You might want to have a list of all possible salutations so that the logic can detect them.

Not sure why that would be a problem. I am not replacing the spaces, just the slashes.

The issues with replacing the slashes with spaces before creating the columns is that a persons surname could be split accross two columns when it should only be the string before the "/"

 

[Leeman]

You might want to have a list of all possible salutations so that the logic can detect them.

Not sure why that would be a problem. I am not replacing the spaces, just the slashes.

If the slashes are replaced with a space then a person with a surname with a space in it would be incorrect. So the first step should be Split the column at the slash and create a new column for the surname. Then perform the split with spaces ojn the remaining string, i.e after the slash.

 

[jal]

If the slashes are replaced with a space then a person with a surname with a space in it would be incorrect. So the first step should be Split the column at the slash and create a new column for the surname. Then perform the split with spaces ojn the remaining string, i.e after the slash.

I think I'm beginning to understand. Ok, so my solution won't work, but you might want to take a look at it, if you need some pointers (attachment).

 

[jal]

Well, it doesn't sound too bad, I think we can just do INSTR to get the position of the slashmark and then use everything up to that point as the surname...

 

[jal]

Okay, here's a revised version. Do lots of testing for bugs. Maybe if I have time tomorrow I'll help you fix any bugs.

 

[Leeman]

Well, it doesn't sound too bad, I think we can just do INSTR to get the position of the slashmark and then use everything up to that point as the surname...

Yes this works extremly well thank you Jal.

One final question. In the orginal column Fullname, is it possible to keep the orginal field contents as they are, because now there is a space between the surname and other names.

i.e in field Fullname Smith John C Mr should be kept as Smith/John C Mr

 

[jal]

Well, I just realized a bug -

This line If i > 1 Then

should be

If i > 0 Then

Please change that.

 

[jal]

Yes this works extremly well thank you Jal.

One final question. In the orginal column Fullname, is it possible to keep the orginal field contents as they are, because now there is a space between the surname and other names.

i.e in field Fullname Smith John C Mr should be kept as Smith/John C Mr

I think the revised version leaves Fullname intact - but be sure to fix that bug i mentioned.

 

[Leeman]

I think the revised version leaves Fullname intact - but be sure to fix that bug i mentioned.

Thanks Jal,

I fixed the bug and it works fine.

If there exists any other chars or strings after the salutation how best to ignore these.

For example if remnant = Bob J Dr (loc) how to ignore the (loc)

            remnant = Mid(rs("Passenger name"), pos + 1)
            words = Split(remnant)
            Dim i As Long
            For i = 0 To UBound(words)
                    word = words(i)
                    If i = 0 Then rs("FirstName") = word
                    If i > 0 Then
                            IsSalutation = False
                             For Each salutation In salutations
                                    If UCase(word) = UCase(salutation) Then IsSalutation = True
                             Next
                             If IsSalutation Then
                                    rs("Salutation") = word
                            Else: numCols = numCols + 1
                                     rs("Other" & numCols) = word
                            End If
                    End If
            Next
            rs.Update

 

[jal]

My suggestion would be to add an Exit For. Just add that one line of code. In other words replace this:

If IsSalutation Then
   rs("Salutation") = word
Else: numCols = numCols + 1
  rs("Other" & numCols) = word
End If

with this:

If IsSalutation Then
   rs("Salutation") = word
    [COLOR=Red]Exit For[/COLOR]
Else: numCols = numCols + 1
  rs("Other" & numCols) = word
End If

This assumes, as you suggested, that the salutation is the end-of-line marker. It won't help on lines with no salutation.

 

[Leeman]

JAL, yes this works fine, and as you correctly mentioned there was an error with a name who had no salutation.

If there is the case for no salutation, then what code should I enter to avoid this issue.


i am assuming it goes in this part

word = words(i)
                    If i = 0 Then rs("FirstName") = word
                    If i > 0 Then
                            IsSalutation = False
                             For Each Salutation In salutations
                                    If UCase(word) = UCase(Salutation) Then IsSalutation = True
                             Next
                             If IsSalutation Then
                                    rs("Salutation") = word
[FONT=monospace]                              [COLOR=Red]Exit For[/COLOR][/FONT]
                             Else: numCols = numCols + 1
                                    rs("Other" & numCols) = word
                            End If
                    End If

A statement that says, "If there is no salutation, then end and go to next record"

 

[jal]

JAL, yes this works fine, and as you correctly mentioned there was an error with a name who had no salutation.

Actually I'm not sure what you mean by error. An error message? I proposed the Exit For to solve this problem:

If there exists any other chars or strings after the salutation how best to ignore these.

You confirmed that this problem is now resolved. You now mention another problem:

If there is the case for no salutation, then what code should I enter to avoid this issue.

If you are still asking me to get rid of extra strings at the end of the line, fine, but how will I know where the end of the line is? The salutation marks the end of the line. In cases where there is no salutation, how will I know where the end is?

Maybe it would help if you gave me a specific example of the "error". I am not understanding what more we need to accomplish here.

 

[boblarson]

And why is this data like this in the first place? Does it come from another system or is it entered that way. If entered that way - why is it designed that way. It is a whole lot easier to pull data together than to split it apart. Just had me wondering about this while watching this all play out.

 

[Leeman]

Hi JAL.
I figured out how to overcome this error by using a count loop that limited to 4 so if there was no salutation then it would go to the next record if it reached the maximum of "other" columns which is limited to 4.

Your assistence was excellent, thank you very much
Regards
Lee