Splitting a field into 2 different fields

  • Thread starter Thread starter AJCB
  • Start date Start date
A

AJCB

Hi,

I have a tricky one...

I have a number (SSCC code) and I have to get whats called a check number.
To do this I have to split the field:

Number=15900164640000314

1 is odd, 5 is even, 9 is odd, 0 is even, 0 is odd, 1 is even and so on.
I then have to add all the odd numbers together and multiply by 3,
then add all the even numbers together. Then add ther odd result to the
even result.

I cannot figure out how to split the numbers?

Can anyone help?

Regards
AJ
 
Create the following tables --
AJCB --
Autonum SSCC code
1 15900164640000314

CountNumber --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Odd_Even --
AutoNUM Odd Even
1 1 2
2 3 4
3 5 6
4 7 8
5 9 10
6 11 12
7 13 14
8 15 16
9 17 18
10 19 20
11 21 22
12 23 24
13 25 26
14 27 28
15 29 30
16 31 32
17 33 34

SELECT AJCB.Autonum, AJCB.[SSCC code], Mid([SSCC code],[Odd],1) AS
Odd_Numbers, Odd_Even.Odd, Mid([SSCC code],[Even],1) AS Even_Numbers,
Odd_Even.Even
FROM AJCB, Odd_Even INNER JOIN CountNumber ON Odd_Even.AutoNUM =
CountNumber.CountNUM
WHERE (((Odd_Even.Odd)<=Len([SSCC code]))) OR (((Odd_Even.Even)<=Len([SSCC
code])));


This is the results --
Autonum SSCC code Odd_Numbers Odd Even_Numbers Even
1 15900164640000314 1 1 5 2
1 15900164640000314 9 3 0 4
1 15900164640000314 0 5 1 6
1 15900164640000314 6 7 4 8
1 15900164640000314 6 9 4 10
1 15900164640000314 0 11 0 12
1 15900164640000314 0 13 0 14
1 15900164640000314 3 15 1 16
1 15900164640000314 4 17 18

You can then add and multiply as needed.
 
off the cuff; you would want to get to know LEN Left and Right vba
methods.

When in VB go to the help and search on these....and then experiment with
them - they're pretty straight forward... they work in queries as a
calculated value...

You could isolate each character using these - in an admittedly cumbersome
manner using a whole bunch of calculated values in a query or in vb memory.

not sure I'm giving good advice - I mean it will definitely work - but there
may be something more creative and efficient out there.....
 
Hi Karl,

Thanks for this, but it is not really what I am trying to achieve.

The titles made a little confusing, as odd and even have nothing to do with
it.

We can use column A and column B as an example.

A B

1 5
9 0
0 1
6 4
6 4
0 0
0 0
3 1
4

29 15

(29*3)+15=102
102/10=10.2
10-2=8

8 is the check number.

Do you think this is possible without using SQL or VB? I have no experience
of these.

If I can get the 29 and the 15, I can work from there.

Regards
Aaron
KARL DEWEY said:
Create the following tables --
AJCB --
Autonum SSCC code
1 15900164640000314

CountNumber --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Odd_Even --
AutoNUM Odd Even
1 1 2
2 3 4
3 5 6
4 7 8
5 9 10
6 11 12
7 13 14
8 15 16
9 17 18
10 19 20
11 21 22
12 23 24
13 25 26
14 27 28
15 29 30
16 31 32
17 33 34

SELECT AJCB.Autonum, AJCB.[SSCC code], Mid([SSCC code],[Odd],1) AS
Odd_Numbers, Odd_Even.Odd, Mid([SSCC code],[Even],1) AS Even_Numbers,
Odd_Even.Even
FROM AJCB, Odd_Even INNER JOIN CountNumber ON Odd_Even.AutoNUM =
CountNumber.CountNUM
WHERE (((Odd_Even.Odd)<=Len([SSCC code]))) OR (((Odd_Even.Even)<=Len([SSCC
code])));


This is the results --
Autonum SSCC code Odd_Numbers Odd Even_Numbers Even
1 15900164640000314 1 1 5 2
1 15900164640000314 9 3 0 4
1 15900164640000314 0 5 1 6
1 15900164640000314 6 7 4 8
1 15900164640000314 6 9 4 10
1 15900164640000314 0 11 0 12
1 15900164640000314 0 13 0 14
1 15900164640000314 3 15 1 16
1 15900164640000314 4 17 18

You can then add and multiply as needed.

AJCB said:
Hi,

I have a tricky one...

I have a number (SSCC code) and I have to get whats called a check number.
To do this I have to split the field:

Number=15900164640000314

1 is odd, 5 is even, 9 is odd, 0 is even, 0 is odd, 1 is even and so on.
I then have to add all the odd numbers together and multiply by 3,
then add all the even numbers together. Then add ther odd result to the
even result.

I cannot figure out how to split the numbers?

Can anyone help?

Regards
AJ
Click to expand...
Click to expand...
 
Do you think this is possible without using SQL or VB? I have no experience
of these.
All you have to do is open a query in design view, click on VIEW - SQL View
and paste the SQL statement. Edit out any hard returns the copying, pasting,
and posting may have inserted. Save.

Create a totals query grouping on the AutoNUM or whatever your primary key
field is.


AJCB said:
Hi Karl,

Thanks for this, but it is not really what I am trying to achieve.

The titles made a little confusing, as odd and even have nothing to do with
it.

We can use column A and column B as an example.

A B

1 5
9 0
0 1
6 4
6 4
0 0
0 0
3 1
4

29 15

(29*3)+15=102
102/10=10.2
10-2=8

8 is the check number.

Do you think this is possible without using SQL or VB? I have no experience
of these.

If I can get the 29 and the 15, I can work from there.

Regards
Aaron
KARL DEWEY said:
Create the following tables --
AJCB --
Autonum SSCC code
1 15900164640000314

CountNumber --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Odd_Even --
AutoNUM Odd Even
1 1 2
2 3 4
3 5 6
4 7 8
5 9 10
6 11 12
7 13 14
8 15 16
9 17 18
10 19 20
11 21 22
12 23 24
13 25 26
14 27 28
15 29 30
16 31 32
17 33 34

SELECT AJCB.Autonum, AJCB.[SSCC code], Mid([SSCC code],[Odd],1) AS
Odd_Numbers, Odd_Even.Odd, Mid([SSCC code],[Even],1) AS Even_Numbers,
Odd_Even.Even
FROM AJCB, Odd_Even INNER JOIN CountNumber ON Odd_Even.AutoNUM =
CountNumber.CountNUM
WHERE (((Odd_Even.Odd)<=Len([SSCC code]))) OR (((Odd_Even.Even)<=Len([SSCC
code])));


This is the results --
Autonum SSCC code Odd_Numbers Odd Even_Numbers Even
1 15900164640000314 1 1 5 2
1 15900164640000314 9 3 0 4
1 15900164640000314 0 5 1 6
1 15900164640000314 6 7 4 8
1 15900164640000314 6 9 4 10
1 15900164640000314 0 11 0 12
1 15900164640000314 0 13 0 14
1 15900164640000314 3 15 1 16
1 15900164640000314 4 17 18

You can then add and multiply as needed.

AJCB said:
Hi,

I have a tricky one...

I have a number (SSCC code) and I have to get whats called a check number.
To do this I have to split the field:

Number=15900164640000314

1 is odd, 5 is even, 9 is odd, 0 is even, 0 is odd, 1 is even and so on.
I then have to add all the odd numbers together and multiply by 3,
then add all the even numbers together. Then add ther odd result to the
even result.

I cannot figure out how to split the numbers?

Can anyone help?

Regards
AJ
Click to expand...
Click to expand...
Click to expand...
 
Ok.. Thanks.
I will give this a go and let you know how I get on.

Thanks again for your response.

AJ

KARL DEWEY said:
of these.
All you have to do is open a query in design view, click on VIEW - SQL View
and paste the SQL statement. Edit out any hard returns the copying, pasting,
and posting may have inserted. Save.

Create a totals query grouping on the AutoNUM or whatever your primary key
field is.


AJCB said:
Hi Karl,

Thanks for this, but it is not really what I am trying to achieve.

The titles made a little confusing, as odd and even have nothing to do with
it.

We can use column A and column B as an example.

A B

1 5
9 0
0 1
6 4
6 4
0 0
0 0
3 1
4

29 15

(29*3)+15=102
102/10=10.2
10-2=8

8 is the check number.

Do you think this is possible without using SQL or VB? I have no experience
of these.

If I can get the 29 and the 15, I can work from there.

Regards
Aaron
KARL DEWEY said:
Create the following tables --
AJCB --
Autonum SSCC code
1 15900164640000314

CountNumber --
CountNUM
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Odd_Even --
AutoNUM Odd Even
1 1 2
2 3 4
3 5 6
4 7 8
5 9 10
6 11 12
7 13 14
8 15 16
9 17 18
10 19 20
11 21 22
12 23 24
13 25 26
14 27 28
15 29 30
16 31 32
17 33 34

SELECT AJCB.Autonum, AJCB.[SSCC code], Mid([SSCC code],[Odd],1) AS
Odd_Numbers, Odd_Even.Odd, Mid([SSCC code],[Even],1) AS Even_Numbers,
Odd_Even.Even
FROM AJCB, Odd_Even INNER JOIN CountNumber ON Odd_Even.AutoNUM =
CountNumber.CountNUM
WHERE (((Odd_Even.Odd)<=Len([SSCC code]))) OR (((Odd_Even.Even)<=Len([SSCC
code])));


This is the results --
Autonum SSCC code Odd_Numbers Odd Even_Numbers Even
1 15900164640000314 1 1 5 2
1 15900164640000314 9 3 0 4
1 15900164640000314 0 5 1 6
1 15900164640000314 6 7 4 8
1 15900164640000314 6 9 4 10
1 15900164640000314 0 11 0 12
1 15900164640000314 0 13 0 14
1 15900164640000314 3 15 1 16
1 15900164640000314 4 17 18

You can then add and multiply as needed.

:

Hi,

I have a tricky one...

I have a number (SSCC code) and I have to get whats called a check number.
To do this I have to split the field:

Number=15900164640000314

1 is odd, 5 is even, 9 is odd, 0 is even, 0 is odd, 1 is even and so on.
I then have to add all the odd numbers together and multiply by 3,
then add all the even numbers together. Then add ther odd result to the
even result.

I cannot figure out how to split the numbers?

Can anyone help?

Regards
AJ
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I have a tricky one...

I have a number (SSCC code) and I have to get whats called a check number.
To do this I have to split the field:

Number=15900164640000314
Click to expand...

Hopefully this is stored in a Text datatype field, not any sort of Number!
1 is odd, 5 is even, 9 is odd, 0 is even, 0 is odd, 1 is even and so on.
Click to expand...

Based on position in the string - i.e. 1 is odd because it's in an odd
position?
I then have to add all the odd numbers together and multiply by 3,
then add all the even numbers together. Then add ther odd result to the
even result.

I cannot figure out how to split the numbers?
Click to expand...

The Mid() function will do this though it would be a narsty complex
expression. A little VBA function would be better:

Public Function Check(SSCC As String) As Integer
Dim iPos As Integer
If Len(SSCC) <> 17 Then
MsgBox "Invalid SSCC"
Exit Function
End If
Check = 0 ' starting point
For iPos = 1 to 17 step 2
Check = Check + Val(Mid(SSCC(iPos, 1)))
Next iPos
Check = Check * 3
For iPos = 2 to 16 step 2
Check = Check + Val(Mid(SSCC(iPos, 1)))
Next iPos
End Function

Untested air code, you may need to tweak it a bit!
 
Back
Top