Variation on 'Proper Case' problem.

[jonno_g]

Hi All,

I find myself faced with a variation on the usage of Proper Case that I can't find any reference to in a search of this section of the forum using keyword vbProperCase. The closest I've found is this thread: Numeric-Alpha propercase problem

I have a text field that I require to be in Proper Case. Getting to that point is no problem. The problem that I have is that some of the text in that field is made up of alpha-numeric strings that we use as equipment numbers. e.g. PU123 is a pump, TA123 is a tank, and so on.

I already use the following code to correct these instances after the whole field has been changed to Proper Case:

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("tblExclude")
 
    RS.MoveFirst
 
    Do While Not RS.EOF
 
[COLOR=seagreen]       'convert the test string to Proper Case[/COLOR]
        strXstring = StrConv(RS!txtExclusion, vbProperCase)
 
        [COLOR=seagreen]'get the length of the test string[/COLOR]
        intXstringLength = Len(strXstring)
 
        [COLOR=seagreen]'get the string containing the test string from the field Title2[/COLOR]
        Me.Title2.SetFocus
        strTitle2 = Me.Title2.Text
 
        [COLOR=seagreen]'find where in the field Title2 the test string resides[/COLOR]
        varXstringStart = InStr(strTitle2, strXstring)
 
        [COLOR=seagreen]'identify the position in the string of the next character after the test string[/COLOR]
        If varXstringStart <> 0 Then
            intContNum = intXstringLength + varXstringStart
        Else
            intContNum = 1
        End If
 
        [COLOR=seagreen]'gets the first character after the test string[/COLOR]
        strContChar = Mid(strTitle2, intContNum, 1)
 
        [COLOR=seagreen]'tests to see if strContChar is an Alpha character,[/COLOR]
[COLOR=seagreen]       'if so then the test string is likely to be a[/COLOR]
[COLOR=seagreen]       'part of a word and must remain in Proper Case.[/COLOR]
[COLOR=seagreen]       'If it's not an Alpha character then the test string[/COLOR]
[COLOR=seagreen]       'is replaced with Upper Case characters.[/COLOR]
        If Not (Asc(strContChar) > 65 And Asc(strContChar) < 91) And Not (Asc(strContChar) > 97 And Asc(strContChar) < 123) Then
            Me.Title2.SetFocus
            Me.Title2.Value = Replace(strTitle2, strXstring, RS!txtExclusion)
        Else
        End If
 
        [COLOR=seagreen]'if the test string appears on its own then it is capitalised as well[/COLOR]
        If varXstringStart > 1 Then
            strPrev = Mid(strTitle2, (varXstringStart - 1), 1)
 
            If strPrev = " " And strContChar = " " Then
                Me.Title2.SetFocus
                Me.Title2.Value = Replace(strTitle2, strXstring, RS!txtExclusion)
            Else
            End If
 
        Else
        End If
 
        [COLOR=seagreen]'moves to the next test string in the table[/COLOR]
        RS.MoveNext
    Loop

As you can see, I have a table of equipment prefixes that is tested against to do the corrections. The code currently checks to see if the test string is followed by a non alpha character, and if so then it will replace it with the test string in capitals as retrieved from the table.

It will also check to see if the test string exists on its own, surrounded by spaces, in which case it will also replace it.

What I can't seem to get my head around, though, is how to get the code to look at the second instance of the test string in the field. This becomes a problem if the field reads "Transfer Pump Pu123", as opposed to "Pu123 Transfer Pump".

The latter will be successfully corrected to read "PU123 Transfer Pump", but the former will be entirely left alone (and rightly so, according to the code in its current state), as the next character after the test string is an alpha character 'Pump'.

Any suggestions on how to get to the second instance of the test string in any given field where it appears more than once would be greatly appreciated.

Cheers,...Jon.

 

[DCrake]

What I would do is to parse the description and the code into two different fields using a query then apply the UCase() to the code and proper case to the description then have another field that concats them back together. However I would be tempted to keep them seperate.

Do your product codes all have the same make up? Alpha Alpha Numeric Numeric Numeric?

David

 

[jonno_g]

What I would do is to parse the description and the code into two different fields using a query then apply the UCase() to the code and proper case to the description then have another field that concats them back together. However I would be tempted to keep them seperate.

That's quite a reasonable suggestion, except that the larger fields (that contain the equipment numbers within them) are not of any fixed format. The equipment number can (and does) appear anywhere within the string and the formatting of the strings is beyond my control to change as they are from a large number of historical documents that are already in existence.

As such, I would need to write code that could detect the presence of the test string within the field, verify that it is a part of an equipment number, separate it out and capitalise it, and then concatenate the strings back together to recreate the text string for the field. Of course the problem there is that if I could achieve the first two steps then most of the battle is won already, but again I can see that I would strike the same problem - "How do you get to the second instance of the test string within the string being tested?"

Do your product codes all have the same make up? Alpha Alpha Numeric Numeric Numeric?

Unfortunately no, those were just the simplest examples that I could give. There is actually a large number of variations on how the equipment numbers may appear - that's why I presently search for the test string and then check the characters immediately to either side of it. This is the only reliable (with the exception of the issue posted above) method that I've been able to find so far.

Also, if at all possible, I would like to avoid adding more fields to an already quite complex form. (I know that they would be hidden from the users, but I still have to deal with them.) Ideally, I would like to do this task entirely with code.

 

[DCrake]

Can you post a variation of the strings you are attempting to parse and indicate any common thread between them. also what the desired result would look like.

David

 

[jonno_g]

Can you post a variation of the strings you are attempting to parse and indicate any common thread between them. also what the desired result would look like.

Thanks for helping out with this, David.

Below is a list of sample strings that need to be converted to Proper Case. It is by no means exhaustive, but it is a reasonable cross section. As you can see, they are already in Proper Case, but that is because they have already been corrected manually (and I don't have the mental capacity to go through them and replicate all of the errors right now - I'm battling a nasty head cold). Does it suffice to say that as an example the string "Tank TA250 Agitator AG250 (M66)", once converted to Proper Case, would read "Tank Ta250 Agitator Ag250 (M66)"?


Detail of Spout from WC-2 (WC 503) to Elevator E1 (EL503)
Transition from EL 506 to WC 509
Typical Holding Down Arrangement for SD75-22 Dehydrator
Storage Tank 50,000L - TA327
Heat Exchanger (3 ATM) - HE239
CR273 General Arrangement
22KV Sub-Station General Arrangement
CR144B Screw
Automatic Pressure Filter - PF38/38B1H - General Assembly & Principal Dimensions
Reactor RE404 25 Tube - sight glass A&D
Decant Feed Tank TA01584
Settling Tanks TA284, TA289, TA279
Centrifuge CE-405
Plate Filter FI420
Reactor RE355N design parameters
Manifold 3 (ST-10279-250-BL)
Tank TA250 Agitator AG250 (M66)
Dust Collector Exhaust Fan FN283N (M81)
Vacuum Pump PU01067
TA232 Phosphoric Acid Tank
Dosing Pump PU233N (M118)
Tray Dryer DR1339
TA01032 Transfer Pump PU01033
Agitator AG01625

To give you a better idea of where the problem comes from, these strings are imported into the database from AutoCAD drawings, where they form a part of the drawing title. Inside the drawing files they are in all capitals, whereas in this database they need to be presented in Proper Case to make them more readable when large reports are printed.

As you can see, there is a large variation in the ways that they appear within the strings. This is why I think I need to create a function that will evaluate each string individually and correct it in turn.

I can't really identify a common thread that links them all, except that they all contain equipment numbers - not that that's very helpful to what we are trying to achieve.

 

[DCrake]

An idea that would get you 95% of the work done, and mind you this is of the top of my head, is to use the split() function using a space as the delimiter to put the string into an array. So

Reactor RE355N design parameters

Would become

Reactor
RE355N
design
parameters

you would then perform an outer loop based on the number of items in the array.
Inside that loop you would have a nested loop that was based on the length of each item in the array. This would test for the existance of a character that was not in the alphabet, such as a number, bracket,hypen,etc. If found it jumps out of the inner loop and converts the array element to UCase(). If not found it would convert the array item to Proper Case.


It would do that for every element in the outer loop. Then at the end it concatenates each element together with a space between each one.

So in the above example you would end up with

Reactor RE355N Design Parameters

Will work on a function and get back to you.

David

 

[DCrake]

Ok here it is.

Public Function CamelCase(AnyStr As String) As String
Dim Alphabet As String
Dim TmpStr As String
Dim IsUpper As Boolean
Dim WordsArray As Variant
Dim i As Variant
Dim strItems As String


Alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

'is there any spaces in the test string
If InStr(AnyStr, " ") = 0 Then
    IsUpper = False
   For n = 1 To Len(AnyStr)
        'is the current character in the allowable character list
        If InStr(Alphabet, Mid(AnyStr, n, 1)) = 0 Then
            IsUpper = True
            Exit For
        End If
   Next
   AnyStr = IIf(IsUpper = True, UCase(AnyStr), StrConv(AnyStr, vbProperCase))
   CamelCase = AnyStr
   Exit Function
Else

    WordsArray = Split(AnyStr, " ")
    
    For i = 0 To UBound(WordsArray)
    
    
        TmpStr = WordsArray(i)
        IsUpper = False
        For n = 1 To Len(TmpStr)
            'is the current character in the allowable character list
            If InStr(Alphabet, Mid(TmpStr, n, 1)) = 0 Then
                IsUpper = True
                Exit For
            End If
        Next
        'Rebuild the string back to its original phrase with correct formatting
        strItems = strItems & " " & IIf(IsUpper = True, UCase(TmpStr), StrConv(TmpStr, vbProperCase))
    Next
    CamelCase = Trim(strItems)
    
End If

End Function

Save this public function in a module.

To test this function create a new query and add the table that contains the field you want to re format. Add this field to the grid and in the next empty column enter

Revised:CamelCase([YourFieldName])

and view the results


David

 

[jonno_g]

WOW, Thanks!!

I was just looking for pointers and suggestions to send me off in the right direction - you've gone above and beyond!

I haven't tested it yet but it looks promising. I do have one question, though - "Camel Case"?!? (I'm guessing here, but is it Proper Case with extra humps?)

 

[jonno_g]

OK, well I've now tested it, and it works a treat - with a few minor exceptions such as Acronyms and Initiallisms. This is perfectly logical when the code is examined, as it is just doing exactly as it is being told to do.

Given that your solution is already so much more elegant than mine, how would you suggest I go about correcting these few exceptions?

I was thinking that this may be a suitable application for the table of exclusions that I had used in the past, as with that approach I could likely also handle other oddities like proprietary words and trade names (e.g. 'DeltaV', which should end in a capital letter), and some other instances where the formatting has regulatory implications (e.g. 'Ex e', which is the abbreviation for an explosion protection method). Also, I need to be able to cope with numbered items - i.e. where the string contains 'No.2' or 'No. 2'

BTW - I also added the characters ,-;:'()/\" to your string 'Alphabet' in that code. It alters the function slightly, but for my purposes it corrects more errors than it introduces.

 

[DCrake]

As I said in my post it should handle 95% of the translations, you were bound to have cetain oddities. However, if you have proprietory words or phrases, which could not be confused with normal English then you could use the replace function prior to passing the recomposed string back. Such as.

strItems = Replace(strItems,"Deltav","DeltaV")

Create a further function called Exceptions()

Public Function Exceptions(AnyString As Sting) As String

Dim sStr As String
   sStr = Replace(AnyString,"Deltav","DeltaV")
   sStr = Replace(AnyString,"NO.2","No. 2")
   sStr = Replace(AnyString,"NO. 2","No. 2")

Exceptions = sStr

End Function

Simply place a series of replace() commands inside the function to cover any abnormalities.

David

 

[jonno_g]

A slight sidetrack, I know, but if I were to want to use your CamelCase function to make corrections to a field across an entire table, how would I go about it?

I have been using your suggestion of Revised:CamelCase([YourFieldName]) in a Select query a lot over the last couple of days and it's been a great help, but it doesn't appear to be quite that straight forward to use it in an Update query.

 

[DCrake]

If you want to do this as a block update then create a new function

Public Function BatchUpdate() 

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset("[B]YourTableNameHere[/B]")

Do Until Rs.EOF
    Rs.Edit
    Rs("[B]YourFieldNameHere[/B]") = CamelCase(Rs("[B]SameFieldNameHere[/B]"))
    Rs.Update
    Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
Debug.Print "Done"
End Function

Then in the immediate window type in

?BatchUpdate

David

 

[jonno_g]

Ah, thankyou!

I hadn't even thought of that approach, I was trying to do it by manually creating an Update query in Access and I was having no success at all.

I'll try your approach tomorrow as I just shut down my Dev box and it's after 7PM here - I'm going home to have dinner!

Thanks again for all your help so far.

Cheers,...Jon.

 

[DCrake]

Another way is to create a new query and bring down the old field in col 1 and in col2 use :Revised:CamelCase([YourFieldName]) then run the query. You should have two cols 1 old 1 new then do a simple copy and paste from 1 col to another.

David