Splitting a text string into the numbers within

[Sharkman1885]

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]

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]

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]

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]

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]

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]

...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]

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

 

[Sharkman1885]

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]

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]

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

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]

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]

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

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]

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.?

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]

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]

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]

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]

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]

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]

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

Can you post the DB with supporting data.?