How to extract numbers from a memo field and sum them.

[Peter Thomson]

I have a memo field with a mixture of numbers and text. I would like to add up all the numeric values to obtain a total. Numbers can be in the format 01 or 1, all numbers are no more than 2 digits, but may not have a space before the text.

[mshmyob]

Give us a little more info or better yet a sample of the data stream.

Are the numbers on different lines or all bundled together?

cheers,

[Peter Thomson]

The numbers are on the same line. Example :
05VGML/03SFML/01GFML-DAIRY FREE MEAL/04FPML

or it could be:

5 VGML 3 SFML 1 GFML Dairy Free Meal 4 FPML.

It all depends on the style of the data inputter.

I need to add up the total of all the numbers from each Memo field.
And (only if possible) exclude numbers before certain text, ie. BBML.

Hope this is clear.

[ADezii]

This one was a little tricky, but I do feel as though I have arrived at a viable solution. First, a couple of assumptions.

• Table Name is Table1 (modify to your needs).
• Field1 is a Field in Table1 of Type MEMO (modify to your needs).
• The Code Logic assumes that only Single or Double Digits can exist in the MEMO Field, interspersed among various Characters including Spaces.
• For each Record, the Query will call on a Public Function via a Calculated Field (Numeric_Total), and return an Aggregate Total of all Digits that exist in the MEMO Field.
• The MEMO Field may be NULL, since the Function will return NULL.
• Any questions, feel free to ask.

1. Function Definition:
   

   Public Function fCalcTotals_2(varMEMO As Variant) As Variant
   Dim lngLenOfString As Long
   Dim lngCharPos As Long
   Dim lngBuild
    
   If IsNull(varMEMO) Then
     fCalcTotals_2 = Null
       Exit Function
   End If
    
   lngLenOfString = Len(varMEMO)
    
   lngBuild = 0        'Initialize
   lngCharPos = 1      'Initialize
    
   'Check every Character in the String (MEMO Field). If the Character is
   'Numeric, see if the next Character is Numeric since a Number can only be
   '1 or 2 Digts. Add to a pre-existing Long Variable to accumulate a Total.
   'This process cannot be performed on the last Character in the String.
   Do While lngCharPos <= lngLenOfString
     If IsNumeric(Mid$(varMEMO, lngCharPos, 1)) Then         'Is the Character Numeric?
       'Is the Character after it Numeric, but not for the last Character?
       If IsNumeric(Mid$(varMEMO, lngCharPos, 2)) And lngCharPos <> lngLenOfString Then
         lngBuild = lngBuild + Val(Mid$(varMEMO, lngCharPos, 2))
           lngCharPos = lngCharPos + 2     '2-Digits, advance Position by 2
       Else        'Character after is not Numeric
         lngBuild = lngBuild + Val(Mid$(varMEMO, lngCharPos, 1))
           lngCharPos = lngCharPos + 1     '1-Digit, advance Position by 1
       End If
     Else
       lngCharPos = lngCharPos + 1         'Non-Numeric, advance by 1
     End If
   Loop
    
   fCalcTotals_2 = lngBuild
   End Function

2. SQL Statement for Query:
   

   SELECT Table1.Field1, fCalcTotals_2([Field1]) AS Numeric_Total
   FROM Table1;

3. Results after Query Execution (Sample Data):
   

   Field1                                   Numeric_Total    
    1 A 2 B 5 HYTR 2       TTRY                   10
   10 20 30 YYYTRRRR 40                          100
   22LLOUUTDSR16 7 9 21HHFF  22                   97
   55FF45    19    2                             121
   F                                               0
   ULKH1LKLKL7        55                          63

[ADezii]

@Peter:
I tested the Code against new newly posted Sample Data in Post #2, and it appears to work quite well. Results are posted below:

Field1                                          Numeric_Total
05VGML/03SFML/01GFML-DAIRY FREE MEAL/04FPML          13
5 VGML 3 SFML 1 GFML Dairy Free Meal 4 FPML.         13

[Peter Thomson]

Wow, thank you. Works perfectly.
First time I have asked for help, and I am so glad I did.

Thank you very much.

[ADezii]

You are quite welcome.