[AccessD] adding control number

I'll have a shot. Assuming always 8-digits how about
Mid(Id,2) & Right(Cstr(Val(Mid(Right("0000" & Cstr(CLng(Val(Mid(Id,1,1)) &
Val(Mid(Id,3,1)) & Val(Mid(Id,5,1)) & Val(Mid(Id,7,1)))*2),4),1,1)) +
Val(Mid(Right("0000" & Cstr(CLng(Val(Mid(Id,1,1)) & Val(Mid(Id,3,1)) &
Val(Mid(Id,5,1)) & Val(Mid(Id,7,1)))*2),4),2,1)) + Val(Mid(Right("0000" &
Cstr(CLng(Val(Mid(Id,1,1)) & Val(Mid(Id,3,1)) & Val(Mid(Id,5,1)) &
Val(Mid(Id,7,1)))*2),4),3,1)) + Val(Mid(Right("0000" &
Cstr(CLng(Val(Mid(Id,1,1)) & Val(Mid(Id,3,1)) & Val(Mid(Id,5,1)) &
Val(Mid(Id,7,1)))*2),4),4,1)) + Val(Mid(Id,2,1)) + Val(Mid(Id,4,1)) +
Val(Mid(Id,6,1)) + Val(Mid(Id,8,1))),1)


--
Andy Lacey
http://www.minstersystems.co.uk




--------- Original Message --------
From: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
To: "AccessD at databaseadvisors.com" <AccessD at databaseadvisors.com>
Subject: [AccessD] adding control number
Date: 26/08/05 09:10


Hello Group,

I have an ID (text, because it begins with 00) that exist of 8 numbers.
I need to change this ID by removing the first 0, and by adding a extra
control number.
This control number can be calculated from this ID. This can be done by
several query’s. Is it possible to do in one query??

For example.

I have ID: 00327833

Take the number of the odd positions: 0 3 7 3

Place these numbers behind each other to make one number: 0373

Multiply this number with 2: 0373 x 2 = 0746

Count up the separate numbers from this last multiplication: 0 + 7 + 4 + 6 =
17

>From the original ID, take the even positions: 0 2 8 3

Count up the separate numbers from the even positions with the sum of the
last count up: 17 + 0 + 2 + 8 + 3 = 30

The last number of the previous sum is the extra control number: 0

>From the original ID, remove the first 0 and add the control number:
03278330


Who can help me with this?

TIA

Pedro Janssen

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

________________________________________________
Message sent using UebiMiau 2.7.2