Solved Nested Instr() Issues (1 Viewer)

Minty

AWF VIP
Local time
Today, 15:39
Joined
Jul 26, 2013
Messages
10,354
I think I am going mad. I am trying to extract a word in a variable-length string after a specific string. The field is imaginatively called Description.
This really needs to work in a query and I'm a bit anti writing a function as it seems overkill.

There is a string I can identify and find that begins with = "FX" , this works;
Code:
TheStartOfFX: InStr(1,[Description],"FX")
At the moment this is returning 18 for all my current data, but I can't guarantee that it always be starting in this position.
I also don't want to rely on the FX text always being the same length - it won't be.

So I need to identify the end of the FX text - the position of the end of the word that begins with "FX" by using the above instr() function, this doesn't work ;
Code:
TheStartOfTheFirstWordAfterFX : InStr(InStr(1,[Description],"FX"),[Description]," ")

If I hard code the starting position of FX into the calculation it works;
Code:
TheStartOfTheFirstWordAfterFX : InStr( 18 ,[Description]," ")

What am I missing out on? I'm positive I have nested Instr() before without these issues.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:39
Joined
Oct 29, 2018
Messages
21,358
What does "doesn't work" mean? Were you getting an error, nothing is showing up, or the wrong thing is showing up, or something else?

PS. I just tried nesting an InStr() function, and it worked fine.

1620228190532.png
 
Last edited:

Minty

AWF VIP
Local time
Today, 15:39
Joined
Jul 26, 2013
Messages
10,354
@theDBguy School-boy mistook - it just says #Error in the query results.

The individual parts return what I would expect to see. It is only when I nest the functions it doesn't work.
a) TheStartOfFX: InStr(1,[Description],"FX") returns the correct result 18
b) TheFirstWordHardCoded : InStr(18,[Description]," ")
c) TheFirstWordAfterFX : InStr(InStr(1,[Description],"FX"),[Description]," ")

1620228668383.png
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
How about using Mid to figure out where to start after FX ?
 

Minty

AWF VIP
Local time
Today, 15:39
Joined
Jul 26, 2013
Messages
10,354
@Isaac That is the plan, but I need to identify the starting point for the Mid, as that string could start in a different place and be longer in the future.

The idea is to use MID([Description],StartOfFX, EndOfFX) to return the FX string in it's entirety.
I could be lazy and fix the lengths, but I know in time it will bite me in the backside.

This is only the start of a chunky bit of string extraction, and the format is quite variable, including peoples names with 2, 3 or 4 portions (Bert Smith , Filipe De Sousa , Pedro de la Rosa etc. etc. that I need to extract.
I don't really want to have to nest five or six queries to get there if one can be made to work.
I ultimately need to get the parts of the first section of the string and parts from the last part of the string, then return the middle section (names) as a final output.

Nesting the instr() and InstrRev() functions were supposed to make this quite simple. :unsure:
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:39
Joined
Sep 12, 2006
Messages
15,613
Can you split the string, and then find the split portion that starts with FX:

Then you get sort of

a = split (fullstring,separator)
then iterate the the a() array until you find the one that starts "FX"
then the string you want is this, where x is the array subscript that starts "FX"

trim(mid(a(x),3))

ie - the rest of string starting with fx, from position 3 onwards, trimmed of leading and trailing spaces.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:39
Joined
Apr 27, 2015
Messages
6,281
Hi there Minty, although I cannot offer any real expertise on the subject, but Regular Expressions sounds like what you need...
 

Minty

AWF VIP
Local time
Today, 15:39
Joined
Jul 26, 2013
Messages
10,354
Thanks all, @gemma-the-husky thinking I'm heading down that route, it's complicated slightly by the lack of a common delimiter, some things are separated by a - some by a " " but I can work around that, along with a word count.

@NauticalGent Reg Ex might work, but I have always found it a bit "black arts"

The other option is to just chuck it all temp table and use a stored procedure and produce the outputs from there, which may well be the better long term solution. Just thought I would use Access at the import stage to save moving stuff around.

@theDBguy Your demo proves it should work, I wonder if it's to do with string length... Nope just checked all under 255 chars.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:39
Joined
Oct 29, 2018
Messages
21,358
@theDBguy School-boy mistook - it just says #Error in the query results.

The individual parts return what I would expect to see. It is only when I nest the functions it doesn't work.
a) TheStartOfFX: InStr(1,[Description],"FX") returns the correct result 18
b) TheFirstWordHardCoded : InStr(18,[Description]," ")
c) TheFirstWordAfterFX : InStr(InStr(1,[Description],"FX"),[Description]," ")

View attachment 91323
Hi. Sorry for the delay and thank you for the additional information. I gave it another try like so:
SQL:
SELECT "help me here" AS description, InStr(1,description,"me") AS fx, instr(fx,description," ") as notnested, instr(instr(1,description,"me"),description," ") as nested;
Here's the result:
1620232730639.png

1620232756003.png

Hope that helps...
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:39
Joined
Jan 23, 2006
Messages
15,364
??TheFirstWordAfterFX : InStr(InStr(1,[Description],"FX"),[Description]," ")
Instr returns an integer, you need to extract a string. Seems you need the string between the above, and the next space and hopefully there aren't 2 contiguous spaces.


Just found this recent (Mar 9/21) comment on similar issue from Doc (it may help with any aversion to UDF)
True, it gets more complex if inside a query. You CAN nest them, except that then you have to repeat several of the InStrRev since you can't so easily trap a return value. Which is why the Access gods created UDFs.
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
@Isaac That is the plan, but I need to identify the starting point for the Mid, as that string could start in a different place and be longer in the future.

The idea is to use MID([Description],StartOfFX, EndOfFX) to return the FX string in it's entirety.
I could be lazy and fix the lengths, but I know in time it will bite me in the backside.

This is only the start of a chunky bit of string extraction, and the format is quite variable, including peoples names with 2, 3 or 4 portions (Bert Smith , Filipe De Sousa , Pedro de la Rosa etc. etc. that I need to extract.
I don't really want to have to nest five or six queries to get there if one can be made to work.
I ultimately need to get the parts of the first section of the string and parts from the last part of the string, then return the middle section (names) as a final output.

Nesting the instr() and InstrRev() functions were supposed to make this quite simple. :unsure:
Oh - from your example codes it didn't look like you had tried the MID yet, just a series of Instr()'s...

Can you post a sample of the actual text - from whence you want to extract the word after the FX
 

Minty

AWF VIP
Local time
Today, 15:39
Joined
Jul 26, 2013
Messages
10,354
??TheFirstWordAfterFX : InStr(InStr(1,[Description],"FX"),[Description]," ")
Instr returns an integer, you need to extract a string. Seems you need the string between the above, and the next space and hopefully there aren't 2 contiguous spaces.


Just found this recent (Mar 9/21) comment on similar issue from Doc (it may help with any aversion to UDF)
@jdraw Sorry maybe the field alias was misleading - I simply wanted the position of the end of the word to ultimately use in the Mid() function, That is/was the problem, not even getting the integer value returned.

I have subsequently knocked up a simple Access function to find the position of a string with an option for occurrences to find, it seems to perform more than adequately on the small dataset's I'm playing with.

Edit:
I'll post up some sample data after I've munged it suitably, it is pretty sensitive.
Be interested to see some alternative approaches.
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
1620237372162.png



Code:
?instr(1,"something something something FX nextword","FX")
 31
?mid("something something something FX nextword",instr(1,"something something something FX nextword","FX")+2)
 nextword
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:39
Joined
Oct 29, 2018
Messages
21,358
View attachment 91341


Code:
?instr(1,"something something something FX nextword","FX")
 31
?mid("something something something FX nextword",instr(1,"something something something FX nextword","FX")+2)
 nextword
I could be wrong, but I think the idea was "FX" could be any length of word that begins with "fx."
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
I could be wrong, but I think the idea was "FX" could be any length of word that begins with "fx."
You could be right - and I may have misunderstood it. In that case I wouldn't even try to put it in a single line--too gross for the next person to easily read.

Code:
Function ExtractIt(strInput As String)
Dim lngStart As Long
lngStart = InStr(1, strInput, "FX")
lngStart = InStr(lngStart + 2, strInput, " ")
ExtractIt = Mid(strInput, lngStart)
End Function
 

Minty

AWF VIP
Local time
Today, 15:39
Joined
Jul 26, 2013
Messages
10,354
Did you see my last post?
Hi yes, sorry, I was creating my reply and then got stuck on a call without refreshing things.

I can't run your query in the database I'm using, but I can in another database. I get an "Expression to Complex" error
I'm now trying to fathom why that is.

@Isaac Yes, I don't want to assume that the strings I'm searching for will stay the length they are currently.
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
@Isaac Yes, I don't want to assume that the strings I'm searching for will stay the length they are currently.
My function should work, then....at least, according to my most recent understanding (which never assumed the original string was any particular length, but rather used INSTR to find "FX" - but the latest function I posted also assumes that "FX" can actually be the start of a word, and not stand alone)

FX [then a space]
FXasdfasdfasdfadsf [then a space]
FXasdfasdfasdfdasfasdfasddf [then a space]

etc
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:39
Joined
Oct 29, 2018
Messages
21,358
Hi yes, sorry, I was creating my reply and then got stuck on a call without refreshing things.

I can't run your query in the database I'm using, but I can in another database. I get an "Expression to Complex" error
I'm now trying to fathom why that is.

@Isaac Yes, I don't want to assume that the strings I'm searching for will stay the length they are currently.
At least now we know you weren't going mad. There must be something off with your db file. Good luck!
 

Users who are viewing this thread

Top Bottom