Breaking up odd numbers

[Edgarr8]

Hey,

I am trying to separate all the characters in the odd number positions.
for example i have

2001234567890

and i want to retrieve

2,0,2,4,6,8,0

I been trying to use mid, but i can only get 1 at a time,

any ideas?

 

[Banana]

Aircode. Input is given.

Dim i As Long
Dim l As Long
Dim s As String

l = Len(Input)

For i = 1 to l
   If Not (i + 1 mod 2) Then
      If Not s = "" Then
           s = ", " & s
      End If
      s = Mid$(Input, i, 1) & s
   End If
Next

 

[georgedwilkinson]

You could also use Banana's approach but put "Step 2" after the "For" statement. You would then be able to eliminate the first "If" statement.

 

[Banana]

Even better, George. Whodunthunkit?

 

[Edgarr8]

Im sorry, but how am i suppose to use that in a query? or even in my table design?

 

[Banana]

Hm. You don't even want it in table design.

For query, you can call a public function with the appropriate field as the argument and use it in query.

Write a function in a standalone module:

Public Function GetOddOut(vInput As Variant)

Dim i As Long
Dim l As Long
Dim s As String

If Not IsNull(vInput) Then
   vInput = CStr(vInput)
   l = Len(Input)

   For i = 1 to l Step 2
      If Not s = "" Then
           s = ", " & s
      End If
      s = Mid$(Input, i, 1) & s
   Next
End If

GetOddOut = s

End Function

Then call it in query like this:

SELECT ColA, GetOddOut(ColA)
FROM aTable;

But... your question about table design worries me. What actually what you want to do with it?

 

[Edgarr8]

well actually its not in a table its in a query, let me show you all i need to do with it...

I have a formula in excel that I am using to come up with the check digit in excel like this:

=MOD(10 - MOD( SUMPRODUCT(MID(A2, {1,2,3,4,5,6,7,8,9,10,11,12,13}, 1) * {3,1,3,1,3,1,3,1,3,1,3,1,3}), 10), 10)

where A2 is equal to 2001234567890

after the calculation i come up with =9

to do this calculation

Step 1. starting at the far left with the first digit, add all the characters in the odd number positions.
2+0+2+41+6+8+0=22
Step 2. Take the number found in step1 and multiply it by 3.
22x3=66
Step 3. starting on the left and beginning the the second digit, add all the characters in the even numbered position.
0+1+3+5+7+9=25
Step 4. Add the answer from steps 2 and 3
66 +25=91

step 5. the check digit is the number which, added to the value obtained in step 4 equals a number which is the next highest multiple of 10. I.E., if the value in step 4 were 68, the next multiple of 10 would be 70.

therefore, 68 +2=70, meaning, the check digit will be 2. if the number is 10, the check digit will be 0.


I want to do this calculation into a query, since in access is where i generate my Gtin numbers. (A2)


and as you can see im still in the first step.

 

[Banana]

Well, you can do all of that in Access, using the same function I just wrote for you. Since you just now mentioned you actually were after sum of odd digits, you'd just omit the concatenating and do a simple summing of the digits for each iteration, then write out the VBA for the step 2 to 5 and return the check digit back to the query so it'll get listed.

If you're not clear, I suggest you try and play with the query in Access using what I already wrote for you above and see how it all come together. I'm sure it'll become clear as you experiment and you can modify the function to work out the logic you outlined.

 

[Edgarr8]

banana you have been a great help, thank you.