Convert two Table fields to ProperCase after creation 1

[Yarbz]

Hi,
I'm a newbie or a want-ta-be, so please forgive my lack of understanding.

I have an MS Access 2003 database that copies and compiles tables from different databases every morning. One of the tables that is re-created each morning has BAD data entry (that will not be changing any time soon).
I need to change two feilds in that table to ProperCase but, don't know how to do it on a table and not through a form.
Plus there are other factors. I need it to over look the "-" "(" "," etc etc.
The two fields that need changing are tbl_Current_Meds.Order and tbl_Current_Meds.Reason and not the whole table.

I've tried to make a function but can't figure out the script or how to trigger it.
Help
Yarbz

 

[SkipVought]

hi,

What does this mean?

I need it to over look the "-" "(" "," etc etc.

I over looked a 4-leaf clover before.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JoeAtWork]

I can't be sure what you mean by "ProperCase", I'm guessing it means that every word starts with a capital letter?

If the data entered has spaces between the words, writing a function that turns this:

"I have a big red dog."

into

"I Have A Big Red Dog."

would not be too difficult. In this case the function would look for spaces and after each space capitalize the next letter.

However, if the data entered is one long string with no spaces in between words, this is practically impossible to do. The difficulty is in writing a function that can figure out how to break up the strings into separate words. The function would need to access a dictionary of all possible words (i.e. the entire English language) to do comparisons against. But the really hard part that makes this all but impossible is an algorithm that knows where one word ends and another begins. For example, something entered as:

"Businessasusual"

to the human eye, the translation would be obvious, as

"BusinessAsUsual"

but how would you write code to identify that "Business" is the first word, rather than "Bus" being the first word, "In" the second word, after which it could go no further because there are no words that start with "Essas" (as far as I can think of).

 

[Yarbz]

To SkipVought, I need the programming script to have ProperCase not recognize or ignore the extra symbols.

To JoeAtWork, Yes ProperCase causes the first letter of each word to be uppercase and the rest of the word to be lowercase. It also recognizes the spaces as the break for the next word, but doesn't recognize the other symbols i.e. ( , - / etc.

When my Doctors enter an order, most enter it in caps only, some lower case only.
the order may be:
NEOMYCN/BACITRCN/POLYMYXN(NEOSPORIN)OINT
ProperCase turns it into
Neomycn/bacitrcn/polymyxn(neosporin)oint
Recognizing only one of the words Neomycn.
And it needs to be
Neomycn/Bacitrcn/Polymyxn(Neosporin)Oint
Thus the code needs to ignore (over look) in this drug the "/" and the "(", ")"

Does anyone know how to write that script, and to only have it apply to two of 40 fields and not convert the whole table, approx 780,000 entries?

Thanks Yarbz

 

[PWise]

how about "-" "(" ","
Update tablename set order=
StrConv(replace(replace(replace(replace(replace(replace(Order,"/"," "),\," "),"-"," "),"("," "),"("," " ),","," "),3) ,
set order=
StrConv(replace(replace(replace(replace(replace(replace(Reason ,"/"," "),\," "),"-"," "),"("," "),"("," " ),","," "),3)

 

[PWise]

sorry sb

Update tablename set order=
StrConv(replace(replace(replace(replace(replace(replace(Order,"/"," "),\," "),"-"," "),"("," "),"("," " ),","," "),3) ,
set Reason =
StrConv(replace(replace(replace(replace(replace(replace(Reason ,"/"," "),\," "),"-"," "),"("," "),"("," " ),","," "),3)

 

[Yarbz]

No, the symbols can not be changed or replaced, just the uppercase and lowercase. Meaning if I change it to remove anything and the orders are printed that changes the order (very bad).
I found a script that may help but I don't know how to impliment it. And how do you target only two feilds in the entire table?

Function MyProperCase(stOneLine As String, iChangeType As Integer) As String
Function MyProperCase(stOneLine As String, iChangeType As Integer) As String

'--------------------------------------------------------
'- This function will convert a string to Proper Case   -
'- The initial letter of each word is capitalised.      -
'- It will also handle special names such as O', Mc and -
'- hyphenated names                                     -
'- if iChangeType = 1, all text is converted to proper  -
'- case, e.g. 'FRED' is converted to 'Fred'             -
'- if iChangeType = 0, upper case text is not converted.-
'- e.g. 'fred' becomes 'Fred', but 'FRED' remains       -
'- unchanged.                                           -
'--------------------------------------------------------

Dim I As Integer
Dim bChangeFlag As Boolean
Dim stResult As String

'-----------------------------------------------------
'- No characters in string - nothing to do           -
'-----------------------------------------------------
If Len(stOneLine) = 0 Then
    MyProperCase = ""
    Exit Function
End If

'-----------------------------------------------------
'- Always set first letter to upper case             -
'-----------------------------------------------------
stResult = UCase$(Left$(stOneLine, 1))

'------------------------------------------------------
'- Now look at the rest of the string                 -
'------------------------------------------------------
For I = 2 To Len(stOneLine)
    
'-----------------------------------------------------
'- If the previous letter triggered a capital, change-
'- this letter to upper case                         -
'-----------------------------------------------------
    If bChangeFlag = True Then
        stResult = stResult & UCase$(Mid$(stOneLine, I, 1))
        bChangeFlag = False
'----------------------------------------------------------
'- In other cases change letter to lower case if required -
'----------------------------------------------------------
    Else
      If iChangeType = 1 Then
         stResult = stResult & LCase$(Mid$(stOneLine, I, 1))
      Else
         stResult = stResult & Mid$(stOneLine, I, 1)
      End If
    End If
    
'-----------------------------------------------------
'- Set change flag if a space, apostrophe or hyphen  -
'- is found                                          -
'-----------------------------------------------------
    Select Case Mid$(stOneLine, I, 1)
    Case " ", "'", "-"
        bChangeFlag = True
    Case Else
        bChangeFlag = False
    End Select
Next I

'-----------------------------------------------------
'- Special handling for Mc at start of a name        -
'-----------------------------------------------------
    If Left$(stResult, 2) = "Mc" Then
        Mid$(stResult, 3, 1) = UCase$(Mid$(stResult, 3, 1))
    End If
    
    I = InStr(stResult, " Mc")
    If I > 0 Then
        Mid$(stResult, I + 3, 1) = UCase$(Mid$(stResult, I + 3, 1))
    End If
   
MyProperCase = stResult

End Function

Thanks and keep'em coming.
Yarbz

 

[randy700]

How about something like...

Public Function ProperCase()
    Dim strInput As String
    Dim strOutput As String
    Dim strSave As String
    Dim x As Integer
    strInput = "NEOMYCN/BACITRCN/POLYMYXN(NEOSPORIN)OINT"
    
    For x = 1 To Len(strInput)
        If x = 1 Then
            strOutput = UCase(Mid(strInput, x, 1))
        Else
            Select Case strSave
                Case "/", "\", "-"
                    strOutput = strOutput & UCase(Mid(strInput, x, 1))
                Case Else
                    strOutput = strOutput & LCase(Mid(strInput, x, 1))
            End Select
        End If
        strSave = Mid(strInput, x, 1)
    Next
    Debug.Print strInput
    Debug.Print strOutput
End Function

Randy

 

[Yarbz]

But... How do I see the results of that?
Please be specific as I'm not 100% on this coding thing.
And how do I target the table with it?

Thanks
Yarbz

 

[PHV]

I've already posted this function sometimes ago.
In a standard code module create the following function:

Public Function myProperCase(strInput)
If Trim(strInput) & "" = "" Then
  myProperCase = strInput
  Exit Function
End If
Dim i As Integer, x As String, strOut, flg As Boolean
flg = True
For i = 1 To Len(strInput)
  x = LCase(Mid(strInput, i, 1))
  If Not IsNumeric(x) And (x < "a" Or x > "z") Then
    flg = True
  ElseIf flg Then
    x = UCase(x)
    flg = False
  End If
  strOut = strOut & x
Next
myProperCase = strOut
End Function

And now the update query:

UPDATE tbl_Current_Meds
SET [Order]=myProperCase([Order]), Reason=myProperCase(Reason)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Yarbz]

PHV, You Rock.
You're right on with less script and it works great.
Got a few glitches like "Pm" or "Patient'S", but I can Live with that.
After more than 16 hrs of reading and searching it is over!
Thank you, thank you, thank you.
Yarbz