Field Digit Sum formula

  • Thread starter Thread starter h2fcell
  • Start date Start date
H

h2fcell

Hello,
I’m using Access 2007 to query a table containing a Number field that I need
to perform a digit sum of.

Example:
NumField DigitSum
200300 5
210 3
15648 24
220301200 10

Any ideas would be appreciated.
 
There's probably a easier way, but a function like the one below will produce
the sum you are looking for...

Public Function DigitSum(NumberIn As String) As Integer
Dim DigitLen As Integer, CheckLen As Integer
Let DigitLen = Len(NumberIn)
Let CheckLen = 1
Let DigitSum = 0
Do While CheckLen <= DigitLen
DigitSum = DigitSum + CInt(Mid(NumberIn, CheckLen, 1))
CheckLen = CheckLen + 1
Loop
End Function

Just call it in your Query as MySum: DigitSum(MyNumberField)

Substituting your number field name and making the result field name
whatever you want.
 
Hello,
I’m using Access 2007 to query a table containing a Number field that I need
to perform a digit sum of.

Example:
NumField DigitSum
200300 5
210 3
15648 24
220301200 10

Any ideas would be appreciated.
Click to expand...

Assuming that the field is a Long Integer (smaller than two billion odd), you
could use this little VBA function:

Public Function DigitSum(lngI As Long) As Long
DigitSum = 0
Do Until lngI = 0
DigitSum = DigitSum + lngI MOD 10
lngI = lngI \ 10
Loop
End Function

You'ld just use a calculated field in your query by typing

DigitSum: DigitSum(NumField)

in a vacant Field cell.

I thought a while about how to do it without code in a query, and it's
possible but would be pretty snarky!
 
John said:
Assuming that the field is a Long Integer (smaller than two billion odd), you
could use this little VBA function:

Public Function DigitSum(lngI As Long) As Long
DigitSum = 0
Do Until lngI = 0
DigitSum = DigitSum + lngI MOD 10
lngI = lngI \ 10
Loop
End Function

You'ld just use a calculated field in your query by typing

DigitSum: DigitSum(NumField)

in a vacant Field cell.

I thought a while about how to do it without code in a query, and it's
possible but would be pretty snarky!
Click to expand...

VBA:

Note: A recursive function is shown just to be different.

Public Function SumOfDigits(lngX As Long) As Integer
If lngX < 10 Then
SumOfDigits = lngX
Else
SumOfDigits = SumOfDigits(lngX \ 10) + SumOfDigits(lngX Mod 10)
End If
End Function

SQL:

tblSumDigits
SDID AutoNumber
SumDigits Text
SDID SumDigits
1 220301200
2 200300

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
....
18 18

qryPrettySnarky:
SELECT SDID, Sum(Val(Mid([SumDigits], I, 1))) AS SumOfDigits FROM
tblSumDigits, tblI GROUP BY SDID;

!qryPrettySnarky:
SDID SumOfDigits
1 10
2 5

Note: Having a Long value for tblSumDigits.SumDigits instead of text can
be used with the expression Sum(Val(Mid(CStr([SumDigits]), I, 1)))

James A. Fortune
(e-mail address removed)
 
How about adding a small table with the numbers 0 to 9 in it as text.

SELECT YourTable.YourField,
SUM(CLng(tableNumber.Numberfield)) as TheSumOfDigits
FROM YourTable INNER JOIN tableNumber
ON YourTable.YourField LIKE "*" & TableNumber.NumberField & "*"
GROUP BY YourTable.YourField

That is about the simplest query solution I can think of.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John said:
Assuming that the field is a Long Integer (smaller than two billion
odd), you
could use this little VBA function:

Public Function DigitSum(lngI As Long) As Long
DigitSum = 0
Do Until lngI = 0
DigitSum = DigitSum + lngI MOD 10
lngI = lngI \ 10
Loop
End Function

You'ld just use a calculated field in your query by typing

DigitSum: DigitSum(NumField)

in a vacant Field cell.

I thought a while about how to do it without code in a query, and it's
possible but would be pretty snarky!
Click to expand...

VBA:

Note: A recursive function is shown just to be different.

Public Function SumOfDigits(lngX As Long) As Integer
If lngX < 10 Then
SumOfDigits = lngX
Else
SumOfDigits = SumOfDigits(lngX \ 10) + SumOfDigits(lngX Mod 10)
End If
End Function

SQL:

tblSumDigits
SDID AutoNumber
SumDigits Text
SDID SumDigits
1 220301200
2 200300

tblI
ID AutoNumber
I Long
ID I
1 1
2 2
...
18 18

qryPrettySnarky:
SELECT SDID, Sum(Val(Mid([SumDigits], I, 1))) AS SumOfDigits FROM
tblSumDigits, tblI GROUP BY SDID;

!qryPrettySnarky:
SDID SumOfDigits
1 10
2 5

Note: Having a Long value for tblSumDigits.SumDigits instead of text can
be used with the expression Sum(Val(Mid(CStr([SumDigits]), I, 1)))

James A. Fortune
(e-mail address removed)
Click to expand...
 
How about adding a small table with the numbers 0 to 9 in it as text.

SELECT YourTable.YourField,
SUM(CLng(tableNumber.Numberfield)) as TheSumOfDigits
FROM YourTable INNER JOIN tableNumber
ON YourTable.YourField LIKE "*" & TableNumber.NumberField & "*"
GROUP BY YourTable.YourField
Click to expand...

John, this won't give the desired result if there are repeating digits: if
YourField is 2222 (or "2222") you'll pick up the 2 row from tableNumber, but
only once.
 
Ah! Of course. Well trash that not-so-brilliant idea.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top