Sum A String

  • Thread starter Thread starter NeonSky via AccessMonster.com
  • Start date Start date
N

NeonSky via AccessMonster.com

Hello,

Would anyone be able to tell me how to sum a string of numbers such as
starting with string 98745, then have it do 9+8+7+4+5 = 33? Thanks!
 
Not an efficient solution, but a fancy one:


eval( Replace( .... Replace( Replace(Replace( initialString, "0", "+0"),
"1", "+1"), "2", "+2) ..., ) "9", "+9") )



Basically, each replace change each singe digit by the same digit but
prefixed with a + sign. Just before the eval kicks in, the new string is
now like:

"+9+8+7+4+5"


and eval just evaluates the expression in the string.




Vanderghast, Access MVP
 
Debug.Print CLng(Left(98745, 1)) + CLng(Mid(98745,2,1)) +
CLng(Mid(98745,3,1)) + CLng(Mid(98745,4,1)) + CLng(Mid(98745,5,1))

However the above only works if you have exactly 5 numerical characters. Any
more, less, or ABCs, it doesn't work.
 
Use this:

Function AddPlus(strInput As String) As Long
Dim lngX as Long
Dim lngLen as Long
Dim strOutPut As string

For lngX = 1 to Len(strInput)
strOutput = strOutPut & Mid(strInput,1) & "+"
Next lngX

strOutPut = Left(strOutput, Len(strOutPut) -1)

AddPlus = Eval(strOutPut)

End Function
 
Hello,

Would anyone be able to tell me how to sum a string of numbers such as
starting with string 98745, then have it do 9+8+7+4+5 = 33? Thanks!
Click to expand...

When you write 'a string of numbers', I'm guessing you don't mean a
string value such as "98745", but a Number value, such as 98745.
They're different.

Copy and past the following function into Module:

Public Function SumNumberValue(ValueIn As Long) As Long
Dim intX As Integer
Dim Total As Integer
Dim intY As Integer

Do While intX <= Len(ValueIn)
intX = intX + 1
intY = Mid(ValueIn, intX, 1)
Total = Total + intY

Loop
SumNumberValue = Total
End Function


You can call it from a query using:
NumberTotal:SumNumberValue([FieldName])

or from an unbound control on a form or report:
=SumNumberValue([FieldName])
 
Got it! Thanks everyone!
Hello,

Would anyone be able to tell me how to sum a string of numbers such as
starting with string 98745, then have it do 9+8+7+4+5 = 33? Thanks!
Click to expand...

When you write 'a string of numbers', I'm guessing you don't mean a
string value such as "98745", but a Number value, such as 98745.
They're different.

Copy and past the following function into Module:

Public Function SumNumberValue(ValueIn As Long) As Long
Dim intX As Integer
Dim Total As Integer
Dim intY As Integer

Do While intX <= Len(ValueIn)
intX = intX + 1
intY = Mid(ValueIn, intX, 1)
Total = Total + intY

Loop
SumNumberValue = Total
End Function

You can call it from a query using:
NumberTotal:SumNumberValue([FieldName])

or from an unbound control on a form or report:
=SumNumberValue([FieldName])
Click to expand...
 
Back
Top