Locating the position of the first capital letter in a string

[GPSPOW]

Can someone tell me how I can build a field to parse a text string like:

ABS.PAT.string.2

where the result would be a field value of just "string.2"?

I have field values where there are variable occurrences of the "." before the last capital letter. So I want to parse the field 2 positions after the right most capital letter.

Thanks

Glen

 

[John Big Booty]

You would need to loop through the string and test if Asc() of that character falls between 41 and 90 inclusive.

 

[GPSPOW]

I am new to this. In a simple way, how do you loop through the string?

Thanks

Glen

 

[John Big Booty]

It might look something like;

Dim IntCount As Integer
Dim IntChr As String

For IntCount = 0 To [URL="http://www.techonthenet.com/access/functions/string/len.php"]Len[/URL](Me.YourTextField) - 1
     IntChr = [URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid[/URL](Me.YourTextField, Len(Me.YourTextField) - IntCount, 1)
          If Asc(IntChr) > 64 and Asc(IntChr) < 91 Then
               MsgBox "First Capital from the right is " & IntChr    [COLOR="Green"]'Insert whatever code you wish to run here [/COLOR]
               Exit Sub
          End If
Next

MsgBox "No Capitals Found"

 

[Brianwarnock]

It's easy to spot others syntax errors

That should be
Intcount = Intcount+1

Brian

Hth

 

[John Big Booty]

It's easy to spot others syntax errors

That should be
Intcount = Intcount+1

Brian

Hth

Good catch Brian Fixed now

 

[ChrisO]

IntCount was being initialised to zero three times.
Dim IntCount As Integer
IntCount = 0
For IntCount = 0 To Len(Me.YourTextField) - 1

IntCount = IntCount + 1 was causing only every second character to be checked.

Assuming a String in passed and a String is returned:-

Public Function ParseIt(ByVal strIn As String) As String
    Dim lngCount As Long
    Dim strChar  As String
    
    For lngCount = Len(strIn) - 1 To 1 Step -1
        strChar = Mid(strIn, lngCount, 1)
        
        If Asc(strChar) > 64 And Asc(strChar) < 91 Then
            ParseIt = Mid(strIn, lngCount + 2)
            Exit For
        End If
    Next lngCount
    
End Function

With the above code, the default return will be a zero length string.


Assuming a Variant in passed and a Variant is returned:-

Public Function ParseIt(ByVal vntIn As Variant) As Variant
    Dim lngCount As Long
    Dim strChar  As String
     
    If Len(vntIn) Then
        For lngCount = Len(vntIn) - 1 To 1 Step -1
            strChar = Mid(vntIn, lngCount, 1)
            
            If Asc(strChar) > 64 And Asc(strChar) < 91 Then
                ParseIt = Mid(vntIn, lngCount + 2)
                Exit For
            End If
        Next lngCount
    Else
        ParseIt = Null
    End If
    
End Function

With the above code, the default return will be a Null.

And so forth for other passed conditions of required arguments and return values.

Chris.

 

[John Big Booty]

Thanks Chris I've don't know what I was thinking when I wrote that (probably about getting out of the office ). I think I was going to use a While loop but then changed to a For/Next loop. Any way the code will now work as advertised. My apologies for the **** up

... and I think I mixed up my decimal and hex ASCII codes :banghead:

 

[John Big Booty]

... now to find someone who can mix me a Pan Galactic Gargle Blaster

...
Its effects are similar to having your brains smashed in by a slice of lemon wrapped round a large gold brick.

Happy Holidays