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.
6 2289
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,
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.
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.
- 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
- SQL Statement for Query:
- SELECT Table1.Field1, fCalcTotals_2([Field1]) AS Numeric_Total
-
FROM Table1;
- 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
@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
Wow, thank you. Works perfectly.
First time I have asked for help, and I am so glad I did.
Thank you very much.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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)....
|
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...
|
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
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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....
|
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
|
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...
| |