Extract a few words

Bumi66

New member
Local time
Today, 09:19
Joined
Jan 15, 2019
Messages
19
Hi Everyone,

I have a query field with up to 10 words,
I only want to extract the first 3 words,
they are separated by space.

How can i mange that ?

Thanks

Holger
 
Hi Holger. Where would you like to perform the extraction? In a query also? Or in form or report? Just curious...
 
I want to do this in the query without VBA coding
 
I want to do this in the query
In that case, you won't be able to use Split() directly. Instead, you could try using a custom function like this one to make it work. For example:
Code:
First3Words: GetMember([FieldName],1) & " " & GetMember([FieldName],2) & " " & GetMember([FieldName],3)
Hope it helps...
 
Thanks Gasman but I want to do this in the query
 
I want to do this in the query without VBA coding
Oh, without using VBA, it would be a little trickier - but not impossible. Hang on...


Edit: Ok, so far, I got the first two words this way:
Code:
Expr1: Left([f1],InStr([f1]," "))
Expr2: Left(Mid([f1],InStr([f1]," ")+1),InStr(Mid([f1],InStr([f1]," ")+1)," "))
I'll keep trying...


Edit2: Ok, this is the simplest way I could try to get the third word (by using the alias from the first two words; otherwise, it could get really long if we have to repeat the two previous expressions):
Code:
Expr3: Left(Mid([f1],Len([expr1] & [expr2])+1),InStr(Mid([f1],Len([expr1] & [expr2])+1)," ")-1)
 
Last edited:
Thanks Gasman but I want to do this in the query

So I would use the same function and put in a function of my own and then pass through the whole string and which part I wanted, as I expect you are now going to say you want each as separate fields?
Code:
Function MySplit(strString as String, iPosition as Integer)

Dim strArray() as string
strArray = Split(strString)
MySplit = strArray(iPosition)

End Function

and in your query

MySplit(fieldname,0), MySplit(fieldname,1),MySplit(fieldname,2)

If you want them all together then in the function make it as DBGuy proposed?
 
@Bumi66: you can nest Instr functions nested in Left function in a query as in
Code:
SplitOff: Left([splitme],InStr(InStr(1,[splitme]," ")+1,[splitme]," "))
but it would be unreliable. You'd need as many nested Instr as there are values to be retrieved, and you'll likely get nothing where there are fewer values than the number of Instr calls or where a value is separated by some other character should that occur. Since you cannot use Split in a query AFAIK, then a custom function is your safest bet as suggested.
 
Awesome Thanks a lot the dbguy, that's exactly what I was looking for (y)
 
Hi. Micron makes a good point. When I was testing my InStr() approach above, the value of [f1] I was using was "one two three four." The expressions I posted above works for this sample data. If I only had "one two three" or event "one two," then those expressions (or some of them) will probably fail. Whereas, using the GetMember() function I recommended earlier would/should be able to handle any missing values. Otherwise, to avoid the potential errors, the expressions would get more complicated because we'll have to add an IIf() condition to make sure the InStr() function won't produce an error.
 
Thanks a lot everyone I use thebdguy's option and it works great

Expr1: Left([Item]![Description],InStr([Item]![Description]," "))

Expr2: Left(Mid([Item]![Description],InStr([Item]![Description]," ")+1),InStr(Mid([Item]![Description],InStr([Item]![Description]," ")+1)," "))

Expr3: Left(Mid([Item]![Description],Len([expr1] & [expr2])+1),InStr(Mid([Item]![Description],Len([expr1] & [expr2])+1)," ")-1)

and finally

Text2: [Expr1] & " " & [Expr2] & " " & [Expr3]

to put it all back together
 
Last edited:
Thanks a lot everyone I use thebdguy's option and it works great
Hi. You're welcome. We were all happy to assist. But please, keep in mind the other concerns we brought up earlier just in case you run into any issues in the future. Good luck with your project.
 
I know I have to look more into VBA Codes there are much more possibilities, but that's a little too complicated for me right now and I don't have the time right now to learn it. So far I get around with Queries and Macros, but my database gets bigger and bigger, I probably don't get around without VBA in the future.

Thanks again
 
That's understandable. No worries. Cheers!
 

Users who are viewing this thread

Back
Top Bottom