Reduce long number to a single one

V

viddom

Hi,
I have a column called CODE with 15-digit-numbers like 452013098230156. I
want to reduce them to a single number as follows
4+5+2+0+1+3+0+9+8+2+3+0+1+5+6=49 then 4+9=13 then 1+3=4
What expression will help me to obtain such numbers either in the same
column or a new one?
The obtained number will be added to the final digit of the code, as follows
4520130982301564
 
M

Martin J

It can probably be done nicer but here's a function that you could use to do
it in a query.

Public Function checkdigit(ds As String) As Integer
Dim b As Integer, c As Integer
While (b = 0) Or (b > 9)
For c = 1 To Len(ds)
b = b + Val(Mid$(ds, c, 1))
Next c
If b > 10 Then ds = Str(b): b = 0
Wend
checkdigit = b
End Function

if code is a string In the query it would look like
newcloumn:checkdigit(
Code: 
). You can use str([code]) if its a an actual #
type.

HTH
Martin J
 
J

Jack MacDonald

Write a custom VBA function that accepts a string value and returns a
numeric value. Call the function with the string conversion of the
seed number and iterate through each character in the string. Call the
function recursively if the return value is greater than 9.



Hi,
I have a column called CODE with 15-digit-numbers like 452013098230156. I
want to reduce them to a single number as follows
4+5+2+0+1+3+0+9+8+2+3+0+1+5+6=49 then 4+9=13 then 1+3=4
What expression will help me to obtain such numbers either in the same
column or a new one?
The obtained number will be added to the final digit of the code, as follows
4520130982301564
Click to expand...


**********************
[email protected]
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
M

Marshall Barton

viddom said:
I have a column called CODE with 15-digit-numbers like 452013098230156. I
want to reduce them to a single number as follows
4+5+2+0+1+3+0+9+8+2+3+0+1+5+6=49 then 4+9=13 then 1+3=4
What expression will help me to obtain such numbers either in the same
column or a new one?
The obtained number will be added to the final digit of the code, as follows
4520130982301564
Click to expand...


You need to create a user defined function to do this.
Here's a quicky one I threw together:

Public Function CastOutTens(strNumber As String) As String
Dim k As Integer
Dim intSum As Integer
Dim intDigit As Integer
Dim strResult As String

For k = 1 To Len(strNumber)
intDigit = CInt(Mid$(strNumber, k, 1))
intSum = intSum + intDigit
Next k

strResult = CStr(intSum)
If Len(strResult) > 1 Then
strResult = CastOutTens(strResult)
End If
CastOutTens = strResult
End Function

Your query can then calculate the new column using this
expression:

CodePlus: CODE & CastOutTens(CODE)
 
V

viddom

Hi Marshall, happy new year!
I did what you told me:
1. I oppened a new module, copyed and pasted the code and named it.
2. I copyed and pasted the expression for the new column.
3. I was able to see it work.
then I went out and I came in again and it didnt worked out. What did I do
wrong? it tells me that CASTOUTTENS is an "undefined function", but I have a
module called CASTOUTTENS. Maybe, I don't know how to create a funcion. Can
you help me on that. Thanks
 
M

Marshall Barton

viddom said:
I did what you told me:
1. I oppened a new module, copyed and pasted the code and named it.
2. I copyed and pasted the expression for the new column.
3. I was able to see it work.
then I went out and I came in again and it didnt worked out. What did I do
wrong? it tells me that CASTOUTTENS is an "undefined function", but I have a
module called CASTOUTTENS. Maybe, I don't know how to create a funcion. Can
you help me on that. Thanks
Click to expand...


That's not the error I would expect, but the problem is
probably because you named the module the same as a
procedure. Change the name of the module to something else
such as mdlCastOutTens,
 
J

John Vinson

it tells me that CASTOUTTENS is an "undefined function", but I have a
module called CASTOUTTENS.
Click to expand...

The Module and the Function must have DIFFERENT names. If you have a
module named CASTOUTTENS containing a function also named CASTOUTTENS,
you'll get this misleading error.

Rename the module to basMaths (or any name which doesn't conflict with
sub or function or other module names) and see if that solves the
problem.

John W. Vinson[MVP]
 
Top