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

Sharkman1885 · Sep 12, 2018

Hey all,

I am trying to split my data in a field and convert the returned values into numbers that i can then do some basic calculations with. Ill give you an example of what I need and what my end goal is.

First I have fields that are set up in a specific way as part of an identification code for an animal. These codes are formatted like the following: 2/7/10 (not a date) the length of the string can vary from a null value to 4 numbers. In this format the data is stored as short text.

What I am trying to do is extract the numbers from between the "/" and then add them to get a single number. This single number is another identification format.

I will eventually want to multiply any of these individual numbers that are >9 by 1000 to get a single number. So the final product of the example above would be 1009.

Val() stops at the first "/" and the Split() function wont even run for me. Any help would be much appreciated. I am running Access 2016

plog · Sep 12, 2018

First, why are your values stored like this? You should fix this issue and store your data in fields that lets you use it like you need to.

With that said, I would create a custom function in a module for this. You pass it the field value, it returns a number. In that function I recommend using the Split function (https://www.techonthenet.com/access/functions/string/split.php). It will put all the values into an array, then you will be able to loop through them, apply any logic you need and add them up to achieve the final number you want.

Minty · Sep 12, 2018

You'll need a function to determine the end result.

There is an excellent demo of using Split and and an array here https://access-programmers.co.uk/forums/showthread.php?t=97750

Have a look and a fiddle with it and come back if you get stuck.

arnelgp · Sep 12, 2018

you create a function in a module:

public functiin fncDecode(p as variant) as variant
dim v, var
dim r as long
p=p & ""
if p<>"" then
var=split(p, "/")
for each v in var
v=clng(v)
if v>9 then
r=r+ (v*100)
else
r=r+v
end if
next v
fncDecode=r
end function

you pasd the field to the function:

m=fncDecode([fieldname])

Sharkman1885 · Sep 12, 2018

Thanks all. Plog, the data is stored like that because that is how the data is taken. They are clip patterns on a crocodilian. stored as single, Double left and double right. each number represents a single scute that was clipped off an animal. The example I gave was for the single field. The second, seventh and tenth scute was clipped.

Minty and Plog Im looking into those links now

Sharkman1885 · Sep 12, 2018

Ok I am still a bit lost. When I try running the split function I get an error saying that 'Split' is an unidentified function. I am attempting to run it in the query. Is split supposed to be used in VBA code as opposed to SQL? If so then I am even more lost than before because I have almost no experience using VBA.

MarkK · Sep 12, 2018

Sharkman1885 said:
...the length of the string can vary from a null value to 4 numbers.

This data would be far easier to work with if each value was in its own row in a related table. This approach, clumping distinct data points together in a single location, breaks basic principles of database normalization, and makes it practically impossible to summarize or analyze your data.
hth
Mark

MarkK · Sep 12, 2018

VBA.Split() won't run in a query because it returns an array, and the syntax of SQL cannot handle array subscripts.
Mark

Sharkman1885 · Sep 12, 2018

MarkK said:
This data would be far easier to work with if each value was in its own row in a related table. This approach, clumping distinct data points together in a single location, breaks basic principles of database normalization, and makes it practically impossible to summarize or analyze your data.
hth
Mark

Mark,
The reason for the data stored the way it is, is that it is infact a distinct sequence (similar to a bar-code or the scarring on a whale's fluke). It is just part of the overall unique scaring of the animal.

isladogs · Sep 12, 2018

Daniel Pineault provides a very simple method of using Split in queries by building a function for it.
See https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

arnelgp · Sep 12, 2018

maybe you are not convinced, because my code is not
formatted, but the good news is that it actually worked.
just tested it.

Code:

Public Function fncDecode(p As Variant) As Variant
    Dim v, var
    Dim r As Long
    p = p & ""
    If p <> "" Then
        var = Split(p, "/")
        For Each v In var
            v = CLng(v)
            If v > 9 Then
                r = r + (v * 100)
            Else
                r = r + v
            End If
        Next v
        fncDecode = r
    End If
End Function

on your query:

Select *, fncDecode([fieldName]) As theValue From yourTableName;

MarkK · Sep 12, 2018

Sharkman1885 said:
Mark,
The reason for the data stored the way it is, is that it is infact a distinct sequence (similar to a bar-code or the scarring on a whale's fluke). It is just part of the overall unique scaring of the animal.

There are correct and incorrect ways to store data. When data is stored correctly, there is no need for Split() in a query.
Mark

Gasman · Sep 12, 2018

I was thinking it only needed to be in separate fields for each row?

MarkK said:
This data would be far easier to work with if each value was in its own row in a related table. This approach, clumping distinct data points together in a single location, breaks basic principles of database normalization, and makes it practically impossible to summarize or analyze your data.
hth
Mark

Gasman · Sep 12, 2018

A bar code is generally used in it's entirety though, is it not.?
I believe it is only due to the processing that you want to do on this data, justifies splitting it into it's separate components.?

Sharkman1885 said:
Mark,
The reason for the data stored the way it is, is that it is infact a distinct sequence (similar to a bar-code or the scarring on a whale's fluke). It is just part of the overall unique scaring of the animal.

MarkK · Sep 12, 2018

Sharkman1885 said:
What I am trying to do is extract the numbers from between the "/" and then add them to get a single number. This single number is another identification format.

I will eventually want to multiply any of these individual numbers that are >9 by 1000 to get a single number. So the final product of the example above would be 1009.

Individual numbers. Consider storing them in individual locations. Also, the smallest location in a database is the ROW, which is why when we add data to a database we always add rows.
Mark

Sharkman1885 · Sep 12, 2018

Gasman said:
A bar code is generally used in it's entirety though, is it not.?
I believe it is only due to the processing that you want to do on this data, justifies splitting it into it's separate components.?

That is correct. The data must be able to be queried piecewise. As these animals are in the wild, when they are seen many times you only see partial data. Maybe it is on a few clipped scutes near the end of the tail maybe it's on the left or right side. That query works.

The reason for pulling the numbers out the way that i want to here is so I can convert the Scute clip Pattern (SCP)sequence we currently use to a SCP that another agency uses. The SCP is converted into a single number in the other agency whereas we use the individuals clips for ours. 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. Currently S, DL, and DR are stored separately. When S, DL and DR are brought together you end up with the full "barcode" for the animal. The math I can do. It's getting the data to the point I can do the math on it is where I am struggling.

Sharkman1885 · Sep 12, 2018

ridders said:
Daniel Pineault provides a very simple method of using Split in queries by building a function for it.
See https://www.devhut.net/2018/04/22/access-using-split-in-a-query/

I tried this but it returned an error after it ran. But at least it ran

Gasman · Sep 12, 2018

Sharkman1885 said:
I tried this but it returned an error after it ran. But at least it ran

There seems to have been an abundance of posts recently where everyone reports 'it produced an error', yet they never say what that error was or where it occurred.:banghead:

Sharkman1885 · Sep 12, 2018

The error just says "#error" in the field after running the query. I couldn't figure out how to get an actual error code from it.

Gasman · Sep 12, 2018

What do you have in design mode for that field.?

Can you post the DB with supporting data.?

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

Registered User.

Banishment Pending

AWF VIP

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

bit cruncher

bit cruncher

Registered User.

MVP / VIP

..forever waiting... waiting for jellybean!

bit cruncher

Enthusiastic Amateur

Enthusiastic Amateur

bit cruncher

Registered User.

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Similar threads

Users who are viewing this thread