Splitting a text string into the numbers within (1 Viewer)

[Gasman]

Can you break that down as you would do in school to show your calculations?

Using the example I gave earlier (plus the rest of the barcode) S 2/7/10 DL 5 DR 2/3 would convert to 1559.

 

[Sharkman1885]

Short Text. Attached is the data for the SCPs that I pulled from the working database.

 

[Sharkman1885]

Can you break that down as you would do in school to show your calculations?

haha sure thing. S 2/7/10 DL 5 DR 2/3 would convert to 1559.

Starting with S. any number less than 9 would be added based on the description I gave. There is a wrinkle in this explanation as the actual data has a certain DR clip that signifies which agency made the clip. Ill explain that at the end of this post.

So singles (S) when less than 9: 2+7=9. 1000s when greater than 9: 1*1000=1000. Add together, 1009.

DLs are 10's. So DL 5 = 50

1009+50=1059

DR is hundreds. So DR 2/3 is 200+300=500

1059+500 = 1559.


Now for the wrinkle. DR7 DR8 and DR9 are all identifiers for who made the clip. I purposely left the clip off to make the explanation easier.

 

[Gasman]

That was not your original explanation? and I don't think Arne's function can cope with all of that.

However he has shown you the basics, so you could adapt it just as you have broken it down for me here.

Only you know the complexities of the calculations?

haha sure thing. S 2/7/10 DL 5 DR 2/3 would convert to 1559.

Starting with S. any number less than 9 would be added based on the description I gave. There is a wrinkle in this explanation as the actual data has a certain DR clip that signifies which agency made the clip. Ill explain that at the end of this post.

So singles (S) when less than 9: 2+7=9. 1000s when greater than 9: 1*1000=1000. Add together, 1009.

DLs are 10's. So DL 5 = 50

1009+50=1059

DR is hundreds. So DR 2/3 is 200+300=500

1059+500 = 1559.


Now for the wrinkle. DR7 DR8 and DR9 are all identifiers for who made the clip. I purposely left the clip off to make the explanation easier.

 

[Sharkman1885]

Others know how to do the calculations as well. I would have to do the calculations for S, DL, and DR separately then add them together. The main goal in the thread though was to figure out how I can extract the number data from the various fields. I will definitely play around with Arne's function as much as I can since VBA is still a bit foreign to me.

 

[Gasman]

So S 2/7/10 is one field and DL 5 DR 2/3 another?

 

[Sharkman1885]

So S 2/7/10 is one field and DL 5 DR 2/3 another?

Correct,

S is a field
DL is a field
and DR is a field

three separate fields

 

[Gasman]

Ok,
You could try amending the function to call it for each type of field, passing the field type as well as the data, and then in the function carry out the required processing, or create 3 simpler functions and call each respectively.?
The DL you would split by a space character, the other two by the / and then do your math.?

 

[arnelgp]

chk the 3 functions and query1.

 

[Gasman]

arnelgp,

Shouldn't DL be split by space and not / ?

chk the 3 functions and query1.

 

[Sharkman1885]

chk the 3 functions and query1.

Arne thank you! I was actually just going through and came up with something similar myself using your original equation. This is much cleaner tho. I am curious, is it possible to have the DR function ignore the 7, 8 and 9s? That is my next (and final) step. The 7,8, and 9's in those fields denotes the agency that made the capture rather than a numerical value. So agency A, B and C would correspond to DR 7, 8, and 9 respectively. it can ether display as a single field A9999 or in separate fields.

 

[arnelgp]

i modified fncDecodeDR() so as to ignore if there
is 7, 8 or 9 on the field.
on the sample database, i added a table, tblAgency (sample only).
i also added another function that will retrieve
the name of the Agency when 7, 8 and 9 are found in [DR] field
from tblAgency.

since some [DR] fields have combinations of agency, i thought
of concatenating the AgencyNames.

you can modify the existing code to suit your need.

again check out Query1, that consumes all the UDFs.

 

[Sharkman1885]

Thank you!