2-questions regarding data manipulate date:

[djclntn]

1) I have this Table where ALL of the text data is in capitals where I only want the first letter to be capital & the following letters to be in small case. E.g. ‘EASTWICK’ --‘Eastwick’. About 8-10 months ago I’d posed this same question this Forum; however I negligently did not keep the formula/instructions. Could someone please give me guidance?



2) I also have a Table with people’s first & last names in one field, ‘John doe’. In Access is there any way you can break up data from 1-fied into 2-fields? E.g. Student Name: ‘John Doe’ – First Name: ‘John,’ Last Name, “Doe.’
Thanks

[djclntn]

Please disregard the word, "date" in the subject -- typo

[orange]

Question 1: see http://www.techonthenet.com/access/f...ng/strconv.php

Question 2: depends - If all of the names involved are single firstname and single lastname, then it's looking good.
If you have names like Charles O'Malley, or Ali vant Goor, Oscar de la Hoya, etc it's rare that you will find a solution. You will find things that do many such names, but google may be your friend.

I have a couple of routines if you'd like them.

A lot of name parsing is dependent on patterns ( how consistent is the incoming data - is there some punctuation)
eg: LastName, FirstName Initial

[orange]

Here's a function I have used regarding ProperCase ( which briefly is your question 1) and it deals with the McDonalds etc (at least to a large degree).

'---------------------------------------------------------------------------------------
' Procedure : fProperCase
' Author    : orange
' Date      : 5/22/2008
' Purpose   : To put names into a "proper case" format. This deals with
'             the Mc  Mac O'Reilly issues.
'
' --Note: Code obtained from http://www.access-programmers.co.uk/forums
' -- It was written by David McAFee
''If any improvements are made to this code, please
'email David McAfee at mcafee_audio@msn.com
'---------------------------------------------------------------------------------------
'
Function fProperCase(AnyText As String) As String
'Convert passed text to all lowercase. Use ProperCase()
'as you would a built-in Access function.

'If any improvement are made to this code, please
'email David McAfee at mcafee_audio@msn.com

   On Error GoTo fProperCase_Error

If Nz(AnyText, "") = "" Then Exit Function 'If passed value is a null, ignore all the stuff below.
Dim intCounter As Integer
Dim OneChar As String
Dim StartingNumber As Integer
StartingNumber = 1

If Right(Left(AnyText, 4), 1) = " " Then 'Check for 3 letter words
'    If MsgBox("In the example '" & AnyText & "', shall I capitalize the three letter word '" & Mid$(AnyText, intCounter + 1, 3) & "' to '" & UCase(Mid$(AnyText, intCounter + 1, 3)) & "' ?", vbYesNo, "Three letter word was found") = vbYes Then
        'Yes was selected, so Capitalize the 3 char's
'        AnyText = UCase(Left$(AnyText, 3)) & LCase$(Mid$(AnyText, 4, 255))
'        StartingNumber = 4
'    Else
'       'No was selected, so only capitalize the first of the 3 char's
       AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))
       StartingNumber = 2
 '   End If
ElseIf Right(Left(AnyText, 3), 1) = " " Then
'    If MsgBox("In the example '" & AnyText & "', shall I capitalize the two letter word '" & Mid$(AnyText, intCounter + 1, 2) & "' to '" & UCase(Mid$(AnyText, intCounter + 1, 2)) & "' ?", vbYesNo, "Two letter word was found") = vbYes Then
        'Yes was selected, so Capitalize the 2 char's
'        AnyText = UCase(Left$(AnyText, 2)) & LCase$(Mid$(AnyText, 3, 255))
'        StartingNumber = 3
'    Else
'        'No was selected, so only capitalize the first of the 2 char's
        AnyText = UCase(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))
        StartingNumber = 2
'    End If
Else
    'First convert to initial cap, followed by all lowercase.
    AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))
    StartingNumber = 2
End If

'Look at each character, starting at the second character.
For intCounter = StartingNumber To Len(AnyText)
    OneChar = Mid$(AnyText, intCounter, 1)
    Select Case OneChar
    
        '...convert the character after dash/hyphen/slash/period/ampersand to uppercase.
        ' Such as A.B.C. Industries, B&B Mfg
        Case "-", "/", ".", "&", "("
        AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
        Case "'" 'Check the character two places after the apostrophe.
            If Mid$(AnyText, intCounter + 2, 1) <> " " Then 'If it is not a space, then capatilize (O'Conner, O'Niel)
                AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            Else
                'Do nothing (Don't , Tom's, haven't)
            End If
        Case "c" ' Take care of the McAfee's, McDonalds & McLaughlins and such
            If (Mid$(AnyText, intCounter - 1, 1) = "M") Then 'Check if Prev Char is an M
                If ((intCounter - 2) < 1) Then 'Check to see if the M was the first character
                    AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
                ElseIf (Mid$(AnyText, intCounter - 2, 1) = " ") Then 'If M wasn't first character, then check to see if a space was before the M
                    AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
                End If
            End If
        Case " "
            Select Case Mid$(AnyText, intCounter + 1, 2)
               Case "de" 'Add any other exceptions here Example: Oscar de La Hoya
                    AnyText = Left$(AnyText, intCounter) & LCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
               Case Else ' Example: A B C Manufacturing
                  AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            End Select
            'If Mid$(AnyText, intCounter + 4, 1) = " " Or ((intCounter + 3) = Len(AnyText)) Then 'Check for 3 letter words
            '    If MsgBox("In the example '" & AnyText & "', shall I capitalize the three letter word '" & Mid$(AnyText, intCounter + 1, 3) & "' to '" & UCase(Mid$(AnyText, intCounter + 1, 3)) & "' ?", vbYesNo, "Three letter word was found") = vbYes Then
                    'Yes was selected, so Capitalize the 3 char's
            '        AnyText = Left$(AnyText, intCounter) & UCase(Mid$(AnyText, intCounter + 1, 3)) & Mid$(AnyText, intCounter + 4, 255)
            '        intCounter = intCounter + 3
            '    Else
            '        'No was selected, so only capitalize the first of the 3 char's
            '        AnyText = Left$(AnyText, intCounter) & UCase(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            '    End If
            'End If
    End Select
Next
'All done, return current contents of AnyText variable.
fProperCase = AnyText

   On Error GoTo 0
   Exit Function

fProperCase_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure fProperCase of Module fProperCaseStuff"
End Function

Here is a test routine. You can add your own test data and adjust as needed.

'---------------------------------------------------------------------------------------
' Procedure : testFProperCase
' Author    : Jack
' Created   : 12/20/2010
' Purpose   : To test fPropercase routine
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testFProperCase()

Dim s As String
   On Error GoTo testFProperCase_Error

s = "JoHN De la Oma'SaNTtro"   'My test case
's = "OscAr De la Hoya"   'other test data
's = "Ali vant'goor"         ' other test data
's = "Ross & Bessie c MCDonald"  'more test data
Debug.Print fProperCase(s)

   On Error GoTo 0
   Exit Sub

testFProperCase_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testFProperCase of Module fProperCaseStuff"
End Sub

[orange]

Here is a procedure to parse first name and last name from a string with 2 name parts separated by a single space.

It is written as a sub procedure and has test data.

' This routine has 4 test records
' It checks and corrects leading and/or trailing spaces
' It checks and corrects multiple (2 or 3) internal spaces and corrects same
' It checks and rejects names with more than 2 name parts
'
' It does a series of debug.print statements showing results of processing steps.

'---------------------------------------------------------------------------------------
' Procedure : FirstLastname
' Author    : Orange
' Date      : 12-08-2012
' Purpose   :  To parse the first name and last name from a string of this general format
'  Lastname Firstname
' That is a string with 2 name parts separated by a single space.
'
' This routine has 4 test records
' It checks and corrects leading and/or trailing spaces
' It checks and corrects multiple (2 or 3) internal spaces and corrects same
' It checks and rejects names with more than 2 name parts
' It uses Proper case (vbProperCase) to Print the First and Last names
' It does a series of debug.print statements showing results of processing steps.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub FirstLastname()
      Dim i As Integer
      Dim iSpaceAt As Integer
      Dim swork As String
      Dim sTest As String
      Dim FName As String
      Dim LName As String
      Dim str(3) As String  ' 4 test records
10       On Error GoTo FirstLastname_Error

20    str(0) = "John Doe"    'valid data
30    str(1) = " Jane smith " 'leading trailing space
40    str(2) = "GeneRal   PurPose" ' multiple intermediate spaces
50    str(3) = "Oscar de la Renta" 'non standard format - reject this guy
60    For i = 0 To UBound(str)
70    Debug.Print str(i)
       'Remove any leading  and/or trailing spaces
80     swork = Trim(str(i))
90     Debug.Print "swork is .." & swork
       'remove any double/triple/replicated internal spaces
100    swork = Replace(swork, "   ", " ") 'replace 3 with 1
110    swork = Replace(swork, "  ", " ")  'replace 2 with 1
120     swork = StrConv(swork, vbProperCase) '
130    Debug.Print "swork is .. after checking internal spaces .." & swork
       
       'check for multiple (more than  2) name parts
140    sTest = InStr(InStr(swork, " ") + 1, swork, " ")
150    If sTest > 0 Then
160    MsgBox "Invalid Format -more than 2 name parts .." & swork, vbOKOnly
170    Debug.Print "Invalid Format -more than 2 name parts .." & swork
180    Else
       ' Get the Firstname
190     iSpaceAt = InStr(swork, " ")
200     FName = Mid(swork, 1, iSpaceAt - 1)
210     LName = Mid(swork, iSpaceAt + 1)
220     Debug.Print "Firstname  " & FName
230     Debug.Print "Lastname  " & LName
240     Debug.Print
250    End If
260   Next i

270      On Error GoTo 0
280      Exit Sub

FirstLastname_Error:

290       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure FirstLastname of Module AWF_Related"
End Sub

Here is the output of the proc

John Doe
swork is ..John Doe
swork is .. after checking internal spaces ..John Doe
Firstname  John
Lastname  Doe

 Jane smith 
swork is ..Jane smith
swork is .. after checking internal spaces ..Jane Smith
Firstname  Jane
Lastname  Smith

GeneRal   PurPose
swork is ..GeneRal   PurPose
swork is .. after checking internal spaces ..General Purpose
Firstname  General
Lastname  Purpose

Oscar de la Renta
swork is ..Oscar de la Renta
swork is .. after checking internal spaces ..Oscar De La Renta
Invalid Format -more than 2 name parts ..Oscar De La Renta

This could be converted to a function, once you understand the vba and processing.

Hope it's helpful.

EDIT: I have adjusted the code to use vbProperCase in statement at line 120.

[djclntn]

Hey Orange,

Although we don’t know each other it’s always good hearing from you. No offense to anyone here; June & you are my favor people here who always come through for me.

Regarding my first question (StrConv) as soon as I click on the link you provided it looked so familiar & I knew how to proceed to get the results I wanted. I’ll be sure to file/save that webpage. With the second question you have me completely lose. First, my knowledge in Access clearly doesn’t begin to understand VBA Coding. I read though all of them which sorry to say made no sense to me – not implying that there’re incorrect. My names in the field is very basic, a first name & a last name. Could there be a simple in which I can separate the first & last name into 2-serperate fields? It’s the reversal of when you have a ‘First Name’ field & a ‘Last Name’ field & you want to combine the 2-names into 1-field. Often times you may see this (or I’ve used it) in a field called, “File As”; there’s a formula where the last name come first, separated with a “,” and then the first name. I’m certain you know what I’m talking about. Could there be any other method to carry out this task?

Bottom line, my first question was way more important to me in working on a project. The second question isn’t a priority at this immediate time, but could be down the road.

Thanks!

[orange]

The sample in post#5 is directly for your string with "FirstName LastName" issue.
You should be able to work through that code.

You can ignore fProperCase and post #4.

If you need help with the vba in post 5, let me know. It really is all contained in the one routine.

Orange