Upper and Lower case changing

[dedwards1960]

I have a field in the table that contains numbers and a mix of upper and lower case words.

I would like to have this to it check for numbers and then makes the first letter in a word upper case and the remainder lower case.

I know you can do it like this >L<????????? but that dow not work if the word lengths are different. Unless I am missing soemthing.

Example of text in field is this:

100 RCSCC MAJOR PAIN

I would like the RCSCC to remain in UPPERCASE and then move to the next work and change the first letter to UPPER CASE and the remaining letters lower case then repeat this on the next word if there is one in the field.

Thanks for all your help.

 

[raskew]

Hi -

Provided the numerical portion of the string comes first, the following should return what you've
described, without regard to word length.

Copy/paste to a new module and call it as described in comments.

Function fStrFix(pstr As String) As String
'*******************************************
'Re:        http://www.access-programmers.co.uk/forums/showthread.php?t=110692
'Purpose:   Return numerical portion of string, following word in vbUpperCase
'           and subsequent words in vbProperCase
'Coded by:  raskew
'Inputs:    from the debug (immediate) window:
'           ? strFix("100 RCSCC MAJOR PAIN")
'Output:    100 RCSCC Major Pain
'*******************************************

Dim strHold As String
Dim strKeep As String
Dim i       As Integer
Dim n       As Integer

   strHold = pstr
   
   'The val() function returns numbers in a string. It stops reading when it
   'encounters a non-numeric character.
   strKeep = Val(strHold)
   i = Len(strKeep) + 2
   strHold = Mid(strHold, i)
   
   ' the StrConv() function returns a converted string (see help file)
   ' vbUpperCase returns all characters in string as UpperCase
   strKeep = strKeep & " " & StrConv(Left(strHold, InStr(strHold, " ") - 1), vbUpperCase)
   
   ' vbProperCase returns first letter of each word in Upper Case with following letters
   ' in lower case
   strKeep = strKeep & " " & StrConv(Mid(strHold, InStr(strHold, " ") + 1), vbProperCase)
   
   strfix = strKeep
   
End Function

HTH - Bob