I came across a function to sum numbers in this thread: http://bytes.com/topic/access/answer...field-sum-them
I tried to use it, and it works fine for numbers without 00.
However, when summing 1500 + 400 if gives 19 as answer.
Please help me to use the function correctly.
Here is a variation of the code from the other post. It will not work on doubles, and there may be other bugs. Use at your own risk :) - Public Function TSCf_CalcTotals(varMEMO As Variant) As Variant
-
Dim lngLenOfString As Long
-
Dim lngTotal As Long
-
Dim strMemo As String
-
'Check for empty memo
-
If varMEMO & "" = "" Then
-
'Memo is empty/null, return null
-
TSCf_CalcTotals = Null
-
Exit Function
-
End If
-
-
'Convert variant to memo
-
strMemo = CStr(varMEMO)
-
-
lngLenOfString = Len(strMemo)
-
Dim l As Long
-
Dim i As Integer 'Store currentsub string length
-
l = 1
-
i = 1
-
Do
-
'Check substring to see if it is numeric
-
If IsNumeric(Mid(strMemo, l, i)) Then
-
'current substring is numeric
-
i = i + 1
-
Do Until Not IsNumeric(Mid(strMemo, l, i)) Or l + i > lngLenOfString + 1
-
i = i + 1
-
Loop
-
'substring is no longer numeric.
-
'Backtrack 1 strep
-
i = i - 1
-
'convert substring to long, and add to total
-
lngTotal = lngTotal + CLng(Mid(strMemo, l, i))
-
l = l + i
-
Else
-
'substring not numeric, move to next
-
l = l + 1
-
End If
-
'Reset i
-
i = 1
-
-
Loop Until l > lngLenOfString
-
TSCf_CalcTotals = lngTotal
-
-
-
End Function
2 1201
Here is a variation of the code from the other post. It will not work on doubles, and there may be other bugs. Use at your own risk :) - Public Function TSCf_CalcTotals(varMEMO As Variant) As Variant
-
Dim lngLenOfString As Long
-
Dim lngTotal As Long
-
Dim strMemo As String
-
'Check for empty memo
-
If varMEMO & "" = "" Then
-
'Memo is empty/null, return null
-
TSCf_CalcTotals = Null
-
Exit Function
-
End If
-
-
'Convert variant to memo
-
strMemo = CStr(varMEMO)
-
-
lngLenOfString = Len(strMemo)
-
Dim l As Long
-
Dim i As Integer 'Store currentsub string length
-
l = 1
-
i = 1
-
Do
-
'Check substring to see if it is numeric
-
If IsNumeric(Mid(strMemo, l, i)) Then
-
'current substring is numeric
-
i = i + 1
-
Do Until Not IsNumeric(Mid(strMemo, l, i)) Or l + i > lngLenOfString + 1
-
i = i + 1
-
Loop
-
'substring is no longer numeric.
-
'Backtrack 1 strep
-
i = i - 1
-
'convert substring to long, and add to total
-
lngTotal = lngTotal + CLng(Mid(strMemo, l, i))
-
l = l + i
-
Else
-
'substring not numeric, move to next
-
l = l + 1
-
End If
-
'Reset i
-
i = 1
-
-
Loop Until l > lngLenOfString
-
TSCf_CalcTotals = lngTotal
-
-
-
End Function
Thanks, this works perfect.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Alfredo Agosti |
last post by:
Hi folks,
I have an Access 2000 db with a memo field.
Into the memo field I put text with bold attributes, URL etc etc
What I need to to is converting the rich text contained into the memo...
|
by: Jeremy |
last post by:
I have a pretty straightforward database that is designed to record
free-form information about products (date, source and a memo field).
This is searched and updated via a form. For new records,...
|
by: PMB |
last post by:
Thank you in advance for any and all assistance.
I'm trying to input data from strings to a memo field. I would like to know
how to check first to see if there is text there currently and if so...
|
by: Mal |
last post by:
I have an ACC 2000 database that has a strange behaviour
I have a small table, with just a few fields...
My report has very simple grouping and sorting, no code bar a NODATA
event.
I have a...
|
by: Matt |
last post by:
I'm having difficulty with trying to update a Access memo field through an
SQL statement where the value I'm trying to pass is longer than 255
characters. The field is being truncated. I'm using...
|
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: Roy |
last post by:
Hi All,
I have a table in A2K application.I get a memo field via download from
a .csv file.The contents are as below:
field name is TicketStatus,
For example,a single ticket,TicketID 12345 ...
|
by: cosmodango |
last post by:
I have spent several days on this. I hope someone can help me. I am using Acccess 2000. I have a memo field where I have cut and pasted bulleted/numbered lists from Microsoft Word. I want to...
|
by: tdw |
last post by:
Hi all,
I have tried a few different methods to accomplish this, but with no luck. I will post the code for the latest attempt at the end of this post.
I work at a land surveying company. This...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |