Split out text string??

spinkung

Registered User.
Local time
Today, 08:45
Joined
Dec 4, 2006
Messages
267
Hi All,

I'm trying to get the right hand part of a text string so i am only left with the depot name.

Here's how i get them...
49 - Manchester
51/CVT - Nuneaton
58/TEJ - Teeside
8 - Park Royal

Here's what i'd like...
Manchester
Nuneaton
Teeside
Park Royal


Can anyone help with a statement in a query that'll acheive this??

Ta, spin.
 
You can use the Mid() function along with the InStr() function to find the position of the "-".
 
Thanks baldy...

i have tried this but i can't seem to get it to work. i keep getting an ivalid procedure call error...

Code:
courier:  IIf([courier]="City Link", [courier], Mid([courier],InStr(1,"-"),20))

where am i going wrong??

Thanks.
 
For starters, I wouldn't alias the field the same as the existing field name. Second, you don't have the arguments right for the InStr() function. You haven't told it where to search.
 
Thanks again.

I'm still getting the same message with...

Code:
Courier Depot: IIf([courier]="City Link",[courier],Mid([courier],InStr(1,[courier],"-"),20))

????

when i just use Courier Depot: InStr(1,[Courier],"-")
i get 0's in every cell??
 
Can you post the db?
 
Hey baldy...

it's a big ol db. i can make a smal version of it with some sample data.

i had made a mistake before, it should be...

Courier Depot: Mid([Depot],InStr(1,[Depot],"-"),20)

..but i don;t think access like the instr() function being in the mid() function. When i take it out and just have...

Courier Depot: Mid([Depot],3,20)

...it works fine????

Is this possible to do or do i need to find another way?


Thanks.
 
You can definitely use the 2 functions together. I just tested this in a test db:

Whatever: Mid([password],InStr(1,[password],"-"))

and it worked fine, though it returned #Error if there was no "-" in the string, since the Mid function can't handle the zero that the InStr will pass in that situation. I could not reproduce it throwing an invalid procedure call error.
 
Hi -

Try rephrasing the Mid()/Instr(), e.g.:

x = "51/CVT - Nuneaton"
? trim(mid(x, instr(x, "-")+1))
Nuneaton

Bob
 
Paul -

I don't get the error you described, although it's a problem that must be dealt with if there's a possibility that no "-" is present.

x = "51/CVT Nuneaton"
? trim(mid(x, instr(x, "-")+1))
51/CVT Nuneaton

Bob
 
Not sure Bob, but I saw the error on any field without an "-" in it. Perhaps something goofy in my test data, as I just tried on a production db and didn't get the error. I don't do much string manipulation, so I probably have it wrong.
 
first, the position of the hyphen is given by
instr(1,myfield,"-")

now we need to take the chars to the right of this, so this is mid, without the size parameter - so it picks up everything to the right.

mid(myfield,instr(1,myfield,"-")+1)

and finally you need to get rid of leading/trailing spaces so its

trim(mid(myfield,instr(1,myfield,"-")+1))

(I think - not tested it - I might have some arguments in the wrong order)

Note - you should be aware there is a potential issue if there is not exactly one hyphen in the string. Indeed, making things reliably robust is always what takes the time
 
Thanks all,

this...

trim(mid(myfield,instr(1,myfield,"-")+1))

...worked a treat.


Many Thanks, spin.
 

Users who are viewing this thread

Back
Top Bottom