473,320 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.
Oct 23 '10 #1
6 2289
mshmyob
904 Expert 512MB
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,
Oct 23 '10 #2
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.
Oct 23 '10 #3
ADezii
8,834 Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcTotals_2(varMEMO As Variant) As Variant
    2. Dim lngLenOfString As Long
    3. Dim lngCharPos As Long
    4. Dim lngBuild
    5.  
    6. If IsNull(varMEMO) Then
    7.   fCalcTotals_2 = Null
    8.     Exit Function
    9. End If
    10.  
    11. lngLenOfString = Len(varMEMO)
    12.  
    13. lngBuild = 0        'Initialize
    14. lngCharPos = 1      'Initialize
    15.  
    16. 'Check every Character in the String (MEMO Field). If the Character is
    17. 'Numeric, see if the next Character is Numeric since a Number can only be
    18. '1 or 2 Digts. Add to a pre-existing Long Variable to accumulate a Total.
    19. 'This process cannot be performed on the last Character in the String.
    20. Do While lngCharPos <= lngLenOfString
    21.   If IsNumeric(Mid$(varMEMO, lngCharPos, 1)) Then         'Is the Character Numeric?
    22.     'Is the Character after it Numeric, but not for the last Character?
    23.     If IsNumeric(Mid$(varMEMO, lngCharPos, 2)) And lngCharPos <> lngLenOfString Then
    24.       lngBuild = lngBuild + Val(Mid$(varMEMO, lngCharPos, 2))
    25.         lngCharPos = lngCharPos + 2     '2-Digits, advance Position by 2
    26.     Else        'Character after is not Numeric
    27.       lngBuild = lngBuild + Val(Mid$(varMEMO, lngCharPos, 1))
    28.         lngCharPos = lngCharPos + 1     '1-Digit, advance Position by 1
    29.     End If
    30.   Else
    31.     lngCharPos = lngCharPos + 1         'Non-Numeric, advance by 1
    32.   End If
    33. Loop
    34.  
    35. fCalcTotals_2 = lngBuild
    36. End Function
  2. SQL Statement for Query:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Table1.Field1, fCalcTotals_2([Field1]) AS Numeric_Total
    2. FROM Table1;
  3. Results after Query Execution (Sample Data):
    Expand|Select|Wrap|Line Numbers
    1. Field1                                   Numeric_Total    
    2.  1 A 2 B 5 HYTR 2       TTRY                   10
    3. 10 20 30 YYYTRRRR 40                          100
    4. 22LLOUUTDSR16 7 9 21HHFF  22                   97
    5. 55FF45    19    2                             121
    6. F                                               0
    7. ULKH1LKLKL7        55                          63
Oct 23 '10 #4
ADezii
8,834 Expert 8TB
@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:
Expand|Select|Wrap|Line Numbers
  1. Field1                                          Numeric_Total
  2. 05VGML/03SFML/01GFML-DAIRY FREE MEAL/04FPML          13
  3. 5 VGML 3 SFML 1 GFML Dairy Free Meal 4 FPML.         13
Oct 23 '10 #5
Wow, thank you. Works perfectly.
First time I have asked for help, and I am so glad I did.

Thank you very much.
Oct 24 '10 #6
ADezii
8,834 Expert 8TB
You are quite welcome.
Oct 24 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Shyguy | last post by:
I want to create two buttons on a form. One would allow the user to Copy the contents of the current records memo field, the other would allow them to print. I set up a report based on the memo...
16
by: Mark | last post by:
Hello. I am attempting to use AppendChunk() to write binary data to a memo field in Access 2000. My initial call to AppendChunk() results in a data type conversion error. Any suggestions? Here...
5
by: klall | last post by:
Hello. I need to extract date information from a memo field entered in the following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004 01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The...
4
by: Mark Reed | last post by:
Hi All, here is what I am trying to achieve. I have a memo field on a form which users will need to add to as and when. I do not want them to be able to edit information which already exists...
6
by: Dave | last post by:
Hope someone can help! I have a memo fiels in which there are a few numbers including dates but what I want to do is extract a number which is 6 figures long. Can anyone help me? Thanks Dave
9
by: RMC | last post by:
Hello, I'm looking for a way to parse/format a memo field within a report. The Access 2000 database (application) has an equipment table that holds a memo field. Within the report, the memo...
5
by: Bluecove | last post by:
After many unsuccessful attempts at this, including the use of software from Sobolsoft, I need help! I have a table comprised of thousands of long records (all in one column which is Memo type)....
2
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd...
1
by: Ibergarden | last post by:
Hello, I need to extract three text lines from a memo field (created by exporting messages from outlook) and separate them into individual fields. Does anybody can help me? Thank you
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.