Deleteing lowercase letters only

[rafi2]

I've been stumped on this and could use some help...

Fields in my table contains some words that are in uppercase and some in lowercase. I would like to delete all the lowercase letters and keep only the uppercase ones.

For example, if a field contains the following text:
CHLOROSULFONIC ACID with or without sulfur trioxide

I would like to change this to:
CHLOROSULFONIC ACID

Can this be done via an automated process?

Thanks!

 

[FoFa]

Well it could be done, and a VBA function jumps to mind. But I can't say it would be easy. You would have to check each letter and determine if it is a lower case (between ASC(97) and ASC(122)) and remove them one at a time. And what if they used proper case "CHLOROSULFONIC ACID with or without Sulfur Trioxide"
could become "CHLOROSULFONIC ACID S T", and as I show here what about the extra spaces?

 

[rafi2]

I know for sure that any text following the uppercase will always be lowercase, so I don't have to worry about extraneous capital letters. I obtained this table from an official source, and I would like to use it in my database, however only the capitalized words are relevant for what I need. Can you provide a couple more hints on how I would write this function?

 

[FoFa]

One other thought if that is 100% true (only lower case follow the upper case) you could then write a function that works from the back of the text forward, and as soon as it finds the first the upper case letter, take from there left and return it.
Function GetPartIwant(TextIn as string) as string
GetPartIwant=""
If len(TextIn) < 1 then exit function
dim LP as integer, ChkIt as integer
for LP = len(TextIn) to 1 step -1
ChkIt = ASC(mid(TextIn,LP,1))
If ChkIt > 64 AND ChkIt < 91 then
GetPartIwant = left(TextIn,LP)
exit function
end if
Next LP
' note if you get here it didn't find any upper case letters
Exit Function

 

[rafi2]

Thanks, this looks like a good approach.

One question, though, how does one run a function on a table? (ie, how do I pass the argument TextIn to the function). I'm a bit new at this, and was initially thinking of using an Update Query to modify the table.

 

[raskew]

Hi -

See if this is helpful.

Function SayUpper(pString As String) As String
Dim blnLast As Boolean
Dim strKeep As String
Dim intHold As Integer
Dim n       As Integer

    blnLast = True
    n = 1
    Do While blnLast
       intHold = Asc(Mid(pString, n, 1))
       If (intHold >= 65 And intHold <= 90) Or intHold = 32 Then
          strKeep = strKeep & Chr(intHold)
          n = n + 1
       Else
          blnLast = False
       End If
    Loop
    SayUpper = Trim(strKeep)

End Function

Place it in a new module and call from the debug (immediate) window with:
? sayUpper("CHLOROSULFONIC ACID with or without Sulfur Trioxide")

It should return:
CHLOROSULFONIC ACID

HTH - Bob

 

[FoFa]

You woukld actually call the function from a query, in your case an update query. In the update too line of the query grid you would call the function passing it the current value.

 

[rafi2]

Thank you so much! It worked perfectly. This table had over 6000 records, and this completely saved me from having to manually touch up each one of them.