Sum of Digits in a string

[rvsebi]

Hi, is my first time here.
I need help to make sum of digits from a string, something like if i have txt1 = "1234567890" to make txt2 = "1+2+3+4+ ...+0".
Thank you in advance!

 

[vbaInet]

Welcome to the forum rvsebi!

Have look into the Split() function, and if there are going to be letters in the mix, also look into the IsNumeric() function:

http://www.techonthenet.com/access/functions/

Some pseudo-code for you:

dim varSplit as variant
dim var as variant

varSplit = split("1234567890"...etc)

for each var in varSplit
    ... somore more code here...
next

 

[pbaldy]

Using the Split() function would require a delimiter, would it not? I'd create a custom function that stepped through the field, using a For/Next loop and the Mid() function.

 

[vbaInet]

Good catch Paul!

 

[rvsebi]

I used already Replace() and removed all letters, in fact i could have only "X" between numbers so i replaced all "X" with "". Now i have only numbers in that string for sure.

 

[vbaInet]

Can you show us an example of the original string?

 

[pbaldy]

You might find this adaptable to use with the original string, adding the + signs.

Public Function ReplaceSpecial(varInput As Variant) As Variant
  Dim strString               As String
  Dim x                       As Integer
  For x = 1 To Len(varInput)
    Select Case Asc(Mid(varInput, x, 1))
      Case 32, 47 To 57, 65 To 90, 97 To 122
        strString = strString & Mid(varInput, x, 1)
    End Select
  Next x
  ReplaceSpecial = strString
End Function

 

[marlan]

Hi,

May I have a try...?

Public Function SumDigits(varInput As String) As Long
  Dim x                       As Integer
  For x = 1 To Len(varInput)
    if  isNumeric(Mid(varInput, x, 1)) Then
        SumDigits= SumDigits + Cint( Mid(varInput, x, 1))
    End If
  Next x
End Function

 

[vbaInet]

We were actually trying to get rvsebi to have a go first marlan But it doesn't hurt to provide a working solution.

I would save the result of Mid() in a String variable before doing numeric check, just so it's called once.

 

[rvsebi]

Thank you for help.
I tried all but didnt work for what i have or i dont know how to do it exactly
To be more specific:
I have a table with 33 fields (NR,Z1,Z2 .... Z31,Total).
After input data in a form this table fill all fields Z1,Z2 ... Z31 with 8,4,0 or X.
Now i need to put on field Total sum of Z1+Z2+ ... Z31 for each NR.
What i did:
In a loop i open a recordset and for each NR i do
rst!Total = rst!Z1+ ... rst!Z31
and
rst!Total = Replace(rst!Total, "X", "0") to replace all X with 0.
Now Total looks like 8880088888008888800888880088888.

How to make Total a sum of all those digits?
I need so much help with this.

 

[marlan]

Now Total looks like 8880088888008888800888880088888.

How to make Total a sum of all those digits?
I need so much help with this.

Hi,

You can send Total to SumDigits() above:
Mid() Function returns a section from a middle of a string, in our case: a section containing 1 character.
Cint() converts this digit into a numeric value, with is summed up into SumDigits.
The above action is done for each character in Total, and SumDigits returns the sum.

You have already removed non-numeric characters from the string, checking if isNumeric is not needed - you can remove this line of code (and the end of the conditional block).

It is all here, ask again if you need any help.

All the best

 

[rvsebi]

Problem solved.
I feel quilty i solved problem before reading last marlan post but your solutions is good also.
What i did is not so advanced like your function but i am glad it works
In a loop i open a recordset and for each NR, checked where each Z1 ...Z31 ="X" to be = "0" after that Total = CLng(rst!Z1) + ... + CLng(rst!Z31)
Thank you all for answers.

 

[vbaInet]

rst!Total = Replace(rst!Total, "X", "0") to replace all X with 0.

Are you 100% sure that it will only ever contain the letter 'X'? Perhaps it might change in the future!
I would go back to using the IsNumeric() check like marlan had it.

 

[rvsebi]

Yes i am sure 100% but i will remember about that IsNumeric()
Thank you