Split A String In A Query (1 Viewer)

CharlesWhiteman · Oct 26, 2011

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 · Oct 26, 2011

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 · Oct 26, 2011

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 · Oct 31, 2011

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

Code:

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

Code:

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 · Oct 31, 2011

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

Brian

CharlesWhiteman · Nov 2, 2011

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 · Apr 19, 2014

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 · Apr 19, 2014

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 · Apr 19, 2014

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

vijay0606 · Jul 28, 2015

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 · Jul 28, 2015

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

mjdemaris · Jul 26, 2016

@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 · Jul 26, 2016

mjdemaris said:
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 · Jul 26, 2016

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

Trond Hoyem · Jun 22, 2021

sxschech said:
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 · Jun 22, 2021

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

Code:

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

Trond Hoyem · Jun 22, 2021

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

Code:

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 · Jun 22, 2021

Trond Hoyem said:
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:

Code:

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

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

Thx for help!

arnelgp · Jun 22, 2021

Trond Hoyem said:
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 · Jun 22, 2021

arnelgp said:
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.

Split A String In A Query (1 Viewer)

Registered User.

Retired

Registered User.

Registered User.

Retired

Registered User.

New member

Retired

New member

New member

Registered User.

Working on it...

Super Moderator

Working on it...

New member

Enthusiastic Amateur

New member

New member

..forever waiting... waiting for jellybean!

New member

Similar threads

Users who are viewing this thread