Expert advise needed on text formating

[RayComp]

Experience data input integrity through bad operator input. Need to force proper input to database as it is also used in reports were formatting is important.

Currently use following which works fine:

txtCity.Text = StrConv(txtCity.Text, vbProperCase) this format port elizabeth to Port Elizabeth
txtCustomerCode.Text = Format(CustomerCode.Text, "00000") this format 12 to 00012
txtemail.Text = lCase(txtemail.Text) this format [email protected] to [email protected]
txtCountryCode.Text = UCase(txtCountryCode.Text) this format gpa to GPA
txtCarrier.Text = UCase(Mid(txtCarrier.Text, 1, 1)) + LCase(Mid(txtCarrier.Text, 2)) this format GAUTENG GP to Gauteng gp

now the experts please help with

How do I get only the first character to uppercase (example above GAUTENG GP) but following uppercase to stay intact)
like how to format GAuTENG GP to Gauteng GP


South African funny names like:
Dries van Zyl John van der Merwe (van der von du) as part of the surname but stay lower case

how do I get input like:
JOHN VAN DER MERWE to convert to John van der Merwe and dries van zyl to Dries van Zyl ( van der as lower case)

How do I get input like need help. expert advise please. To Need help. Expert advise please (in other words uppercase at start of sentence and after the .)

[leinad31]

There's no sense in trying to make the application intelligent... you can only go so far before you reach a brick wall.

If they are so particular with the output of the result (obsessive compulsive bosses ^^) then they should hire someone to screen the DB data on a regular basis and make the necessary corrections. StrComp(..., ..., vbTextCompare) can be used to ensure that the data wasn't changed, just the case of the characters. Database design then becomes important... so that changes can be facilitated and propagated easily (say through references instead of having multiple copies of the same text).

You might also want to consider using a static list of zip codes, country codes, cities, etc in the database (since these rarely change). The data entry can then be through dropdown lists intead of typed inputs.

If the number of carriers is small and the list hardly changes then you could also make these dropdown; the list obtained from a DB table.

[MartinLiss]

Const TEST_DATA = "GAuTENG"
    
    MsgBox Left$(TEST_DATA, 1) & LCase(Mid$(TEST_DATA, 2))

Const TEST_DATA = "dries van der zyl"
    Dim strTemp As String
    Dim intPos As Integer
    
    strTemp = StrConv(TEST_DATA, vbProperCase)
    intPos = InStr(UCase(strTemp), "VAN ")
    If intPos > 0 Then
        strTemp = Left$(strTemp, intPos - 1) & LCase(Mid$(strTemp, intPos, 1)) & Mid$(strTemp, intPos + 1)
    End If
    intPos = 0
    intPos = InStr(UCase(strTemp), "DER ")
    If intPos > 0 Then
        strTemp = Left$(strTemp, intPos - 1) & LCase(Mid$(strTemp, intPos, 1)) & Mid$(strTemp, intPos + 1)
    End If
    
    MsgBox strTemp

[BruceG]

Marty has provided good code to handle proper case in general as well as examples for specific situations. leinad31 has a good point in having as much of the data entry from drop-down lists as possible.
For the free text stuff, the only additional suggestion I can make is, if feasible, to create a database table of exceptions which would consist of the verbiage and a corresponding code (e.g. "U" for uppercase, "L" for lowercase, etc.), which you could implement during validation. This would only make sense if you could identify a manageable number of exceptions you would be most likely to encoutner (it would obviously be impractical if not impossible to store every conceivable name).