How to pull out chunks of a field with a deliminator character

[dzirkelb]

I have a field that will contain information in the following format:

data-data1-data2-data3

Each chunk of data will be variable in length, but they will always be seperated by the - field.

I am able to get the first chunk out, and the last chunk out, but just can't wrap my brain around how to get the middle ones out (data1, data2).

Here is an example piece of data:

4457-55-9-22325

I would like to know what I can put in my access query (must be in a query as there are reports already built on this) that will pull out 4457 as a result, 55, 9, and 22325 all as seperate results.

Thanks for the help

And, before anyone says to split them into seperate fields or anything of that nature, trust me, I tried, but the powers at be require the data entry to be as it is above, and I am unable to change it.

 

[Brianwarnock]

You will need to use nested instr with to find the positions of the - and the mid function to select the data. You could write a function using Split to create an array based on the - as a separator and then select the cell for the required part of the string.

Brian

 

[dzirkelb]

I would be open to usign both approached, but my Access skills are lacking. I know exactly what you are referring to as that is exactly what I do for weba applications (split the field into an array, then call each portion of the array), but I just don't know how to do it in a query format.

I also do not know the syntax / how to do it for the mid, instr, left, right, len, etc combination that will be needed.

Any actual code examples are appreciated, and where to put them also.

 

[Brianwarnock]

Just kicking this out from memory on my iPad so cannot check anything, lookup split in VBA help to check syntax etc
I would use the function approach something like

Function fgetdata(mystring as string, n as long) as string

Dim myarray as variant

Myarray = split(mystring ,"-")
Fgetdata = myarray(n-1)

End function

In the query

Result: fgetdata(mystring, n)

N is the data you want ie a number 1-4

The function is placed in a module of different name to the function

I am not around tomorrow

Brian

 

[dzirkelb]

Thanks Brian! I am confident that I will get this to work with the code you provided, it is exactly what I was looking for. I'll work on this tomorrow, and post the code results once completed.

 

[Brianwarnock]

Ok I will look forward to a happy result as I spend tomorrow walking in the country, isn't retirement great

Brian

 

[dzirkelb]

very nice

 

[dzirkelb]

Here is the function:

Function GetData(data As String, n As Long) As String

Dim arrayData As Variant
arrayData = Split(data, "-")
GetData = arrayData(n - 1)
End Function

And here is what is used on the query:

CustPO: GetData([CUST PO #],1)

Pretty sure it is exactly what you sent, but I just changed the variable names to fit my coding style. Thanks for your help, works perfectly!

 

[Brianwarnock]

Yes it is what I wrote but that makes me a bit chuffed that I got it correct
I expected you to change the names , for what it's worth I always precede my own functions with an f as in fGetData so that I know they are a UDF, also it allows me to code modifications to Access functions eg fDatediff.

Brian

 

[dzirkelb]

Good practice. For functions, that is the only name where I don't have anythign in front of it, and it starts with a capital letter, just how I do it