Converting or Splitting Contact Name into First And Last When Updating a Table 3

[Johnnycat1]

All,

I am trying to run some code that splits a Contact Name field into two separate fields. This is what I have so far. I don't usually manipulate data this way so I am stumbling around. Your help is greatly appreciated.

Excuse the table and field naming methods... This is a very old table that I created many years ago before learning an appreciation of good naming methods. I have too many links to this now and haven't spent the time to go back and re-name everything.

If Nz(Me![To], 9) = 9 Then Exit Sub
If Nz(Me![Addresses-Contact Names Sub-Form]![Contact Name], 8) = 8 Then Exit Sub
    
Dim StXXX As String
Dim stContactName As String
Dim stFirstName As String
Dim stLastName As String
Dim rstOutlookContact As DAO.Recordset

StXXX = "XXX"
stContactName = Me![Addresses-Contact Names Sub-Form]![Contact Name]
stFirstName = Right$(Me![Addresses-Contact Names Sub-Form]![Contact Name], Len(Me![Addresses-Contact Names Sub-Form]![Contact Name]) - InStr(1, Me![Addresses-Contact Names Sub-Form]![Contact Name], ",") - 1)
stLastName = Left$(Me![Addresses-Contact Names Sub-Form]![Contact Name], InStr(1, Me![Addresses-Contact Names Sub-Form]![Contact Name], ",") - 1)

  Set rstOutlookContact = CurrentDb.OpenRecordset("AddressBookTempOutlookContacts", dbOpenDynaset)

 With rstOutlookContact
  .FindFirst "[ID]='" & StXXX & "'"
    .Edit
    ![LastName] = stLastName
    ![FirstName] = stLastName
    ![LastName] = stLastName
    ![ContactName] = stContactName
    .Update
 End With

 

[Johnnycat1]

Sorry me again...

I have replaced the original code with a new bit of code that is sort of working. It is returning a truncated string but I cannot figure out how to make it delete everything before the space for LastName and everything after the space for FirstName.

This is what I have...

If Nz(Me![To], 9) = 9 Then Exit Sub
If Nz(Me![Addresses-Contact Names Sub-Form]![Contact Name], 8) = 8 Then Exit Sub
    
Dim StXXX As String
Dim stContactName As String
Dim stFirstName As String
Dim stLastName As String
Dim rstOutlookContact As DAO.Recordset

StXXX = "XXX"
stContactName = Me.[Addresses-Contact Names Sub-Form]![Contact Name]
stLastName = Right(stContactName, InStr(Replace(stContactName, " ", "", 1, 1), " ") - 1)

Set rstOutlookContact = CurrentDb.OpenRecordset("AddressBookTempOutlookContacts", dbOpenDynaset)

 With rstOutlookContact
  .FindFirst "[ID]='" & StXXX & "'"
    .Edit
    ![LastName] = stLastName
    ![FirstName] = stLastName
    ![LastName] = stLastName
    .Update
 End With

 

[TheAceMan1]

How are ya Johnnycat1 . . .

Have a look at the [blue]Split[/blue] function.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Untested but something like

Public Function getLastName (varName as variant) as string
  dim aNames() as string
  if not isnull(varName) then
    varName = trim(varName) 'make sure nothing on beginning or end
    aNames = split(varName, " ") ' assume a space between first and last
    if ubound(aNames) = 1 then 'Make sure there are at least 2 names
      getLastName = aNames(1)
    end if
  end if
end function

Public Function getFirstName (varName as variant) as string
  dim aNames() as string
  if not isnull(varName) then
    varName = trim(varName)
    aNames = split(varName, " ") ' assume a space between first and last
    getFirstName = aNames(0)
  end if
end function

 

[Andrzejek]

I would provide 2 text boxes, one for first name, second for the last name.

Otherwise how will you handle names like: John Mac Gregor ?

There are way too many variations of names to handle them all in one text box, IMHO.

Have fun.

---- Andy

 

[TheAceMan1]

Andrzejek . . .

[blue]I am trying to run some code that [purple]splits[/purple] a Contact Name field [purple]into two separate fields[/purple].[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Andrzejek]

The issue remains for names such:
[tt]
Barbara Van Dyke
John Mc Gregor
Jeffery Von Brown
Darrel Ver Houl
Ace T Man One[/tt]

How do you decide in code which one is First name, and Last name?

Have fun.

---- Andy

 

[TheAceMan1]

Andrzejek . . .

Thats 3 textboxes ... not 2.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[randy700]

I have yet to see a first name that has a space in it.
Andrzejek's solution would put the first name (before the 1st space) in one text box.
Everything else (after the 1st space) becomes the last name, including spaces.


Randy

 

[Johnnycat1]

MajP

Your code is simple and works like a charm. The only thing that I did was to change it from a public sub to a private sub since my intent is to use this to populate the Contacts table that is linked to Outlook.

This is what I ended up with and it works perfectly... Thanks

If Nz(Me![To], 9) = 9 Then Exit Sub
If Nz(Me![Addresses-Contact Names Sub-Form]![Contact Name], 8) = 8 Then Exit Sub
    
Dim varContactName As Variant
Dim stFirstName As String
Dim stLastName As String
Dim aNames() As String
Dim StXXX As String

    varContactName = Trim(Me.[Addresses-Contact Names Sub-Form]![Contact Name])   'make sure nothing on beginning or end
    aNames() = Split(varContactName, " ") ' assume a space between first and last
    If UBound(aNames()) = 1 Then 'Make sure there are at least 2 names
      stLastName = aNames(1)
      stFirstName = aNames(0)
    End If

StXXX = "XXX"

Dim rstOutlookContact As DAO.Recordset

  Set rstOutlookContact = CurrentDb.OpenRecordset("AddressBookTempOutlookContacts", dbOpenDynaset)

 With rstOutlookContact
  .FindFirst "[ID]='" & StXXX & "'"
    .Edit
    ![LastName] = stLastName
    ![FirstName] = stFirstName
    .Update
 End With

 

[MajP]

When I write code I like to keep it modular and reusable. So no need to rewrite the code and simply call the functions. It is a lot cleaner that way.

Dim stFirstName As String
Dim stLastName As String
Dim StXXX As String

stLastName = getlastName(Me.[Addresses-Contact Names Sub-Form]![Contact Name])
stFirstName = getFirstName(Me.[Addresses-Contact Names Sub-Form]![Contact Name])
...

The whole reason to write them as functions like that is so that they can be reused in code, used in a query or used in a calculated field on a form


Ex query
Select [Contact Name], getFirstName([Contact Name]) as FirstName, getLastName([Contact Name]) as LastName from ....
or to update the entire table
Update someTable Set LastName = getLastName([Contact Name]), FirstName = getFirstName([Contact Name])...


Ex form control control source
= getFirstName([Contact Name])

BTW, as pointed out this can fail on Double first names (Mary Jane, Billy Bob) last names with spaces (Van Dyke). You may want to check for those with a function and then handle them manually.

Public Function isCompound (varName as variant) as boolean
  dim aNames() as string
  if not isnull(varName) then
    varName = trim(varName)
    aNames = split(varName, " ") ' assume a space between first and last
    isCompound = (ubound(aNames) > 1)
  end if
end function