Split A String In A Query

[CharlesWhiteman]

I have a colums with many rows of the following type of data:

3MIL001- 3mil

I want to split the string at the "-"

I am trawling the posts but not finding exactly what I want so any advice most welcome.

Thanks

 

[Brianwarnock]

If all of the strings are of identical format you need only use Left and Right functions but if the lengths are variable then Instr to find the position of - and Len may be needed and possibly use Mid instead of Right, note that Mid without a length specified uses the rest of the field.

Brian

 

[sxschech]

If the format of your string is as in the example, you could use this:

3MIL001- 3mil

left(x,instr([your_field_name],"-")-1) to give you 3MIL001

trim(mid(([your_field_name],instr(([your_field_name],"-")+1)) to give you 3mil

or the split function
trim(split(([your_field_name],"-")(x))

in the split function, put in 0 for the first item 1 for the next etc.

trim(split(([your_field_name],"-")(0)) will give you 3MIL001

trim(split(([your_field_name],"-")(1)) will give you 3mil

the trim is to remove the extra space

 

[CharlesWhiteman]

Thanks all for your posts. In the end I found the followng which worked really well.

First: I created a module calling it a different name to the actual funtion

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, "-", -1)
ParseText = var(X)
End Function

And then in my query I used the following syntax

ClientName: ParseText([ClientCodeNameString],1)

Where ClientCodeNameString is the value to split and 1 is the position

I also found theh followng useful for getting rid if unwanted text and a space at the start

ExprCleanClientName: Trim(Replace(([ClientName]),"(No longer acting)",""))

 

[Brianwarnock]

Written as if you worked it all out by your self without reading syschech's post.

Brian

 

[CharlesWhiteman]

Just followed the instructions and posted so that others can benefit too. If it comes across as though I created from scratch then please be advised it was created from code I found and then adjusted accordingly.

 

[abaus]

Hello,

I'm new to Access, I admit. Here's my problem, and I would appreciate any advice. I've been reading the posts, but can't quite find what I need -- and it's likely just me being new.

Ok. I have free text strings where I'm searching for two specific key words: "fall" or "fell". I'd like to return 15 characters on both sides of either key word using a query. I've gotten in the ball-park, but not really close. Any advice would be appreciated. Thank you.

 

[Brianwarnock]

You are going to need to write a function for this. You will need to use 2 Instr to find the starting point of either keyword and then subtract 15 from this to find the start of the data required using mid function with a length of 34 .
You will need to check that the start is within the field I.e. whether the Instr returns <15 and adjust if it does.

Brian

 

[abaus]

Brian -- Thank you. I'll try that today.
Adam

 

[vijay0606]

Hi,
Thanks for the info. The first option works fine. I get an error message saying "Undefined function 'split' in expression, while attempting second option. Any luck?

 

[sxschech]

Do you have an example of what isn't working?

 

[mjdemaris]

@sxschech:

I know this is an old post, but today's IS provide data longevity.

left(x,instr([your_field_name],"-")-1) to give you 3MIL001

You forgot to include the starting point for InStr: Left(x,InStr(1,("FieldName"), "-", -1))

But, I like the solution!

 

[Galaxiom]

You forgot to include the starting point for InStr: Left(x,InStr(1,("FieldName"), "-", -1))

The InStr starting position argument is optional and defaults to 1.

 

[mjdemaris]

"I see," said the blind man to the deaf dog.

 

[Trond Hoyem]

If the format of your string is as in the example, you could use this:

3MIL001- 3mil

left(x,instr([your_field_name],"-")-1) to give you 3MIL001

trim(mid(([your_field_name],instr(([your_field_name],"-")+1)) to give you 3mil

or the split function
trim(split(([your_field_name],"-")(x))

in the split function, put in 0 for the first item 1 for the next etc.

trim(split(([your_field_name],"-")(0)) will give you 3MIL001

trim(split(([your_field_name],"-")(1)) will give you 3mil

the trim is to remove the extra space

Hi

I am a rather newbie Access developer. I am currently working on a project where I need to split a string at a "-". Not very different from the example in this thread. I am doing this as a calculated field in a query, as what I need is to find the number following the "-" and then find the highest one.

The code I have in the calculated field is:
Highest: split(( Skjemaer![frmAddNewOffer]![txtOfferNumber] ; "-")(1))

From what I understand, the (1) should get whatever is following the "-", right?

I am not getting any results, just an error message saying that I have an illegal punctuation og paranteses. Anyne see where I am making the mistake?

 

[Gasman]

Try a , instead of a ;, even if that is your system setting?
No need for the outer brackets I would have thought?

Highest: split([Skjemaer]![frmAddNewOffer]![txtOfferNumber] ;"-")(1)
or
Highest: split([Skjemaer]![frmAddNewOffer]![txtOfferNumber] ,"-")(1)

 

[Trond Hoyem]

Try a , instead of a ;, even if that is your system setting?
No need for the outer brackets I would have thought?

Highest: split([Skjemaer]![frmAddNewOffer]![txtOfferNumber] ;"-")(1)
or
Highest: split([Skjemaer]![frmAddNewOffer]![txtOfferNumber] ,"-")(1)

Still the same error message. I have tried both your suggestions.

I realize that the function split() is not included in the Functions in the Build-dialog box. Might it be that it is not included in all versions of Access?

 

[Trond Hoyem]

Still the same error message. I have tried both your suggestions.

I realize that the function split() is not included in the Functions in the Build-dialog box. Might it be that it is not included in all versions of Access?

OK, I found the solution.

I was looking at the wrong field, but not sure if that is the reason. I tried this version instead:

Highest: Mid([offerNumber];InStr(1;[offerNumber];"-")+1)

This worked, I was able to get the info I need from it.

Thx for help!

 

[arnelgp]

I realize that the function split() is not included in the Functions in the Build-dialog box. Might it be that it is not included in all versions of Access?

i have 2003 and split() function is available.
you can't use it in Query because it uses Index.

 

[Trond Hoyem]

i have 2003 and split() function is available.
you can't use it in Query because it uses Index.

Ah!
So thats the reason why it did not work.

OK, learn something new every day.