Selecting uppercase part of field

[LELE7]

Hi,
I have a field with text that is partly uppercase & partly lowercase. I'm trying to select only the uppercase part of the field.

for ex. a field with long description for products. Each description has a part that is not applicable- which is lowercase. I only want the uppercase part of each description.

Any ideas? I am mostly familiar with SQL so solutions in SQL would also be helpful. Thanks.

[TheSmileyCoder]

This code should do the trick. There may be more efficient ways of doing it, but this will work. If your product string has and seperators (Example XLARAR-arar) then you can also do a split which is more efficient.

getUpper=Split("strInput","-")(0)

Split returns an array, the (0) select first element in that array.

Public Function getUpper(strInput As String) As String
    Dim intI As Integer
    Dim strUpper As String
    Dim strChar As String
    
    For intI = 1 To Len(strInput)
        strChar = Mid(strInput, intI, 1)
        If StrComp(strChar, UCase(strChar), vbBinaryCompare) = 0 Then
            strUpper = strUpper & strChar
        Else
            Exit For
        End If
    Next
    
    getUpper = strUpper
End Function

[NeoPa]

From your description I see no way of handling this in SQL. However, your description doesn't explain the situation we need to look at very fully. Perhaps you could provide some example data for us. This is often very revealing as to the actual situation you're dealing with when the ability to explain in detail is limited. You'd be surprised at how often that is.

SQL could be used if there are other distinctions than simply the case of any characters. We'd need those details. Split will (unfortunately) not work directly within the SQL as it involves array processing. Not supported within SQL.

[LELE7]

Thanks NeoPa, that's my answer for SQL. Yes, the only distinction is the uppercase.
TheSmileyOne- I'm really new to programming & not so familiar with actual programming in Access. How do I implement such code once I save it in a Module?

[TheSmileyCoder]

Once implemented in a module, you can simply implement it in SQL.

SELECT getUpper[myField] as ID from myTable;

That will run the function within the the query.

If your using the graphical query editor within Access, it looks a little different.
Example:

ID: getUpper([myField])

[TheSmileyCoder]

The main difference between "native" SQL and SQL that involves VBA functions is that the ladder is somewhat slower. That said, you'd need to be working on 10.000+ records at a time to even notice the difference with such a simple function as the one provided.

[NeoPa]

Thanks NeoPa, that's my answer for SQL. Yes, the only distinction is the uppercase.

I could ask a bunch of precise questions to get the information I'm after, but example data just makes it so much quicker & easier. Never mind. Smiley has you covered anyway.