Solved Nested Instr() Issues

[Minty]

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;

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 ;

TheStartOfTheFirstWordAfterFX : InStr(InStr(1,[Description],"FX"),[Description]," ")

If I hard code the starting position of FX into the calculation it works;

TheStartOfTheFirstWordAfterFX : InStr( 18 ,[Description]," ")

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

 

[theDBguy]

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.

 

[Minty]

@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]," ")

 

[Isaac]

How about using Mid to figure out where to start after FX ?

 

[Minty]

@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.

 

[gemma-the-husky]

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]

Hi there Minty, although I cannot offer any real expertise on the subject, but Regular Expressions sounds like what you need...

 

[Minty]

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]

@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:

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:

Hope that helps...

 

[jdraw]

??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.

 

[gemma-the-husky]

@Minty
Easier if you can give us a generic string to work on I think.

 

[Isaac]

@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.

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]

??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.

 

[theDBguy]

Be interested to see some alternative approaches.

Did you see my last post?

 

[Isaac]

?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]

View attachment 91341

?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]

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.

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]

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]

@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]

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!