Solved Access - Counting Letters and Numbers In A String (1 Viewer)

EzGoingKev

Registered User.
Local time
Today, 18:59
Joined
Nov 8, 2019
Messages
178
Good afternoon.

I have some data like this:

AB12
ABC1
ABC12
ABCD123EF

I would like to count letters, numbers, and then letters again if applicable. This would give me:

AB12 = 22
ABC1 = 31
ABC12 = 32
ABCD123EF = 432

I am not sure if this something I can do in a query using LEN along with LEFT/MID/RIGHT/INSTR or if it would require a function be created in VBA.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:59
Joined
Oct 29, 2018
Messages
21,358
I think you're going to need a custom function to do that.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:59
Joined
Feb 28, 2001
Messages
27,001
Note that you did not specify whether your outputs would be numbers or digit strings. I am going to assume that you want digit strings, not an actual number. (But of course if you wanted a number, you could just use a CLng or CInt of the digit string.)

Look here:


The class module described in the link will support this kind of counting if you make one function to encapsulate the operation. Use the instructions in the document in that zip file to add the parser to your project. Then, in a separate general module, add code that does this. (And you can clean up the spacing - I shot this one from the hip so the comments will probably not line up at all.)

Code:
Public Function CountStrings( stInput As String ) As String
Dim Prs as New ParserObj
Dim CSResult As String

    Prs.OptAN = FALSE       'disallow mixed alphanumeric strings
    Prs.OptQS = FALSE       'disallow quoted strings
    Prs.ParseLine = stInput       'give parser the input string
    CSResult = ""                     'assume no strings
    While Prs.AtEOL = FALSE     'while we still have input....
        Prs.ParseNext                  'parse another segment
        Select Case Prs.TkCode     'check type of string content
            Case 1, 2                       'for cases 1 and 2 (alphabetic and numeric digits)....
                CSResult = CSResult & CStr( Prs.TkSize )    'tack on the size of the substring
            Case Else                      'ignore anything else
        End Select
    Wend                                    'when done,....
    Return CSResult                    'return the answer
End Function

Now when you want this conversion, just use stX = CountStrings( "ABC123DEF" ) and you will get "333" (If I did that right.)

It will also work in a query if the function is declared publicly in a general module.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:59
Joined
May 7, 2009
Messages
19,169
Code:
'arnelgp
Public Function CountChars(ByVal sText As String) As Long
    Dim sTemp As String
    Dim ln As Integer
    Dim i As Integer, j As Integer
    Dim k As Integer, m As Integer
    Dim z As Integer
    Dim arrNum() As Integer
    sText = sText & vbNullString
    ln = Len(sText)
    If ln < 1 Then Exit Function
    ReDim arrNum(0 To 19)
    For i = 1 To ln
        sTemp = Mid$(sText, i, 1)
        If z < 1 Then
            j = 0: k = 1
            If IsAlpha(sTemp) Then
                z = 1
            ElseIf isNumeral(sTemp) Then
                z = 2
            Else
                z = 3
            End If
            arrNum(j) = k
        Else
            If IsAlpha(sTemp) Then
                m = 1
            ElseIf isNumeral(sTemp) Then
                m = 2
            Else
                m = 3
            End If
            
            If z <> m Then
                j = j + 1
                k = 1
            Else
                k = k + 1
            End If
            z = m
            arrNum(j) = k
        End If
    Next
    ReDim Preserve arrNum(0 To j)
    sTemp = ""
    For i = 0 To UBound(arrNum)
        sTemp = sTemp & arrNum(i)
    Next
    Erase arrNum
    CountChars = CLng(sTemp)
End Function

Private Function IsAlpha(ByVal sTest As String) As Boolean
    Const Letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    IsAlpha = Letter Like "*" & sTest & "*"
End Function

Private Function isNumeral(ByVal sTest As String) As Boolean
    Const Numbers = "0123456789"
    isNumeral = Numbers Like "*" & sTest & "*"
End Function

Debug.Print CountChars("ABCD123EF")
Result: 432
 

Sun_Force

Active member
Local time
Tomorrow, 07:59
Joined
Aug 29, 2020
Messages
396
Code:
'arnelgp
Public Function CountChars(ByVal sText As String) As Long
    Dim sTemp As String
    Dim ln As Integer
    Dim i As Integer, j As Integer
    Dim k As Integer, m As Integer
    Dim z As Integer
    Dim arrNum() As Integer
    sText = sText & vbNullString
    ln = Len(sText)
    If ln < 1 Then Exit Function
    ReDim arrNum(0 To 19)
    For i = 1 To ln
        sTemp = Mid$(sText, i, 1)
        If z < 1 Then
            j = 0: k = 1
            If IsAlpha(sTemp) Then
                z = 1
            ElseIf isNumeral(sTemp) Then
                z = 2
            Else
                z = 3
            End If
            arrNum(j) = k
        Else
            If IsAlpha(sTemp) Then
                m = 1
            ElseIf isNumeral(sTemp) Then
                m = 2
            Else
                m = 3
            End If
            
            If z <> m Then
                j = j + 1
                k = 1
            Else
                k = k + 1
            End If
            z = m
            arrNum(j) = k
        End If
    Next
    ReDim Preserve arrNum(0 To j)
    sTemp = ""
    For i = 0 To UBound(arrNum)
        sTemp = sTemp & arrNum(i)
    Next
    Erase arrNum
    CountChars = CLng(sTemp)
End Function

Private Function IsAlpha(ByVal sTest As String) As Boolean
    Const Letter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    IsAlpha = Letter Like "*" & sTest & "*"
End Function

Private Function isNumeral(ByVal sTest As String) As Boolean
    Const Numbers = "0123456789"
    isNumeral = Numbers Like "*" & sTest & "*"
End Function

Debug.Print CountChars("ABCD123EF")
Result: 432
@arnelgp
Code:
sText = sText & vbNullString
I've seen so many times you add a null string to a string. What does it mean and what is the benefits?

Many thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:59
Joined
May 7, 2009
Messages
19,169
this is simpler:
Code:
'arnelgp
Public Function CountAlphaNumeric(ByVal sText As Variant) As Long
    Dim var As Variant
    Dim v As Variant
    Dim tmp As String
    sText = sText & vbNullString
    If Len(sText) < 1 Then Exit Function
    tmp = "0"
    With CreateObject("VBScript.RegExp")
            .Pattern = "([0-9]+)|([a-zA-Z]+)"
        .Global = True
        .IgnoreCase = True
        .MultiLine = True
        Set var = .Execute(sText)
        For Each v In var
            'Debug.Print v
            tmp = tmp & Len(v)
        Next
    End With
    CountAlphaNumeric = Val(tmp)
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:59
Joined
May 7, 2009
Messages
19,169
my bad, should be:

Public Function CountChars(ByVal sText As Variant) As Long

i concat it to vbNullString (""), since a Null value can be passed (in the case of field value).
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:59
Joined
Aug 30, 2003
Messages
36,118
I did this yesterday, never hit post. I post it now for the regex challenged (me included):

Code:
Public Function CountChar(varInput As Variant) As Variant
  Dim strString               As String
  Dim x                       As Integer
  Dim booNumeric As Boolean
  Dim intCurCount As Integer
 
 
  If Len(varInput & vbNullString) > 0 Then
    booNumeric = IsNumeric(Mid(varInput, 1, 1))   'establish starting value
 
    For x = 1 To Len(varInput)
      If booNumeric = IsNumeric(Mid(varInput, x, 1)) Then
        intCurCount = intCurCount + 1
      Else
        strString = strString & intCurCount
        booNumeric = IsNumeric(Mid(varInput, x, 1))
        intCurCount = 1
      End If
    Next x
    strString = strString & intCurCount
  End If
 
  CountChar = strString
End Function
 

EzGoingKev

Registered User.
Local time
Today, 18:59
Joined
Nov 8, 2019
Messages
178
Thanks for the replies everyone.

Doc - I imported the class module and put the code from your reply in a module. The "Return CSResult" turned red as soon as I pasted it.

I wound up using pbaldy's solution and it worked great.

Thanks again for the help.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:59
Joined
Aug 30, 2003
Messages
36,118
I wound up using pbaldy's solution and it worked great.

Happy to help!

That line in Doc's code was not actual code. It would be:

CountStrings = CSResult
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 09:59
Joined
Jan 20, 2009
Messages
12,849
I would really encourage you to all read this post very carefully and look at the demo. I explain a fundamental technique that I hope will change the way you think about problems like this.
I am not sure if this something I can do in a query using LEN along with LEFT/MID/RIGHT/INSTR or if it would require a function be created in VBA.
Generally most developers confronted by this actually quite complex problem would make a custom function as have the other posters. The problem with these techniques is that all the data is fed through what is effectively a tiny hole, a single character at a time.

Arnelg's Regular Expression is no exception, it just has the tiny hole hidden below the surface in the Regex. We discovered this a while back when Isladogs compared RegEx against custom VBA functions after I suggested RegEx in a solution. RegEx is actually slower, probably because it is so powerful and is able to recognise a vast variety of patterns. rather than be focused on the simple pattern required.

These techniques are what is known by database professionals as RBAR (pronounced "re-bar") which means "Row By Agonising Row". It isn't efficient because it doesn't make use of the database engine's ability to process data as sets and it has to call the function which is interpreted code not like the native functions in SQL. Consequently, while the function works well on small numbers of records it is relatively slow on a large dataset.

The database I have attached shows how to do this task using a query that only employs database engine capabilities to get the results. It is not an especially pretty example of the technique. I just threw together something that worked in this situation. I expect it could be nicer if I gave it some more thought (or used TSQL). I would also normally write this as embedded subqueries but it is much easier to see how it works in stages.

The crux is the query GetChar which uses what is called a Tally table. A Tally table is just a set of records with integers. (There are several ways to manifest them without storing a table but this one is just a simple table.) Using a Cartesian Product (ie no join) to the data, the Tally table is used to drive Mid() , returning a record for every character in the data along with its recordID and its position in the record.

Basically this process completely shreds the data and tags it. This is not an intuitive thing for a human to do trying to solve this problem but it is the way database engines function best. Lots of records, few columns and simple relationships.

The rest of the queries just manipulate the data into the form we need. It is complicated a little by the variable number of groups.

GetAlpha and GetNum separate the Alpha and Numeric records from GetChar using Like "[0-9]" for Numerals and Like "[a-z]" for characters.

GetLastAlpha and GetLastNum find the last alpha and numeric characters in the groups by checking if the subsequent character in a record is the opposite type. It can only be viewed in SQL because of the join type is not supported in the designer. They then Union the last Alpha and Numeric characters in the string (respectively) because they are not followed by an opposite type. GetLastAlpha also Unions a Null for each record. Without it, none of the two group results are returned in the subsequent query. It is much simpler than dealing with what I expect would be some really awful logic.

Results puts the combinations of character positions back into single records. The two group and three group variations of those with three groups are both returned. A more general solution with an arbitrary number of groups might use a Crosstab but it wouldn't be straightforward.

Final rejects the two group results for those with three groups, calculates the Answer and joins back to the data so it can be displayed with the results.

I have not clocked it against the VBA functions but I have little doubt it would hugely outperform the functions on large numbers of records. Anyone care to add a heap of records and import the functions to see? I need to go to bed.
 

Attachments

  • CountChars.mdb
    304 KB · Views: 249

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:59
Joined
Feb 28, 2001
Messages
27,001
My bad... that syntax (RETURN value) only applies to VB, not to VBA. If I recall, I threw that function together after midnight.

pbaldy supplied the correct response for VBA.
 

EzGoingKev

Registered User.
Local time
Today, 18:59
Joined
Nov 8, 2019
Messages
178
No worries.

I am not a developer. 95% of the what I can do I learned on the fly to resolve an issue.

If I can copy/paste something and it works then great. If it does not work I look at it. In some cases I understand the coding and can trouble shoot. In most cases I cannot.
 

isladogs

MVP / VIP
Local time
Today, 22:59
Joined
Jan 14, 2017
Messages
18,186
Until prompted by @Galaxiom today, I'd completely forgotten about running a speed test comparing regex with a different approach.
see Range of ASCII values in criteria | Page 2 | Access World Forums (access-programmers.co.uk) - posts #23 onwards

I've never really got to grips with using Regex and find it fairly challenging.
Undoubtedly, Regex is very powerful but it is also comparatively slow.

if if have time, I'll compare the speed of each approach used here: VBA by @pbaldy, @arnelgp's Regex version & the use of queries suggested by @Galaxiom and report back.
Luckily I have my own code for generating long lists of random alphanumeric strings
 
Last edited:

Users who are viewing this thread

Top Bottom