Solved Nested Instr() Issues

[Minty]

Here is a sample

Payment PI1234567-FXTESTDATA12 John Smith AWF International Ltd - AC002233444 FXTESTDATA12 Expenses Claim 123/124

Payment PI0123456-FXTESTDATA12 John Doe AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 789/790/791

Payment PI3467890-FXTESTDATA12 Long John Silver AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 456/455/456/457

Payment PI2345678-FXTESTDATA12 Vini De Le Vinci AWF International Ltd - AC002233444 FXTESTDATA12 Expenses Claim 130

I can't assume that the PI reference or FX reference will stay the same length hence trying to use Instr() to get the positions.

 

[Isaac]

Here is a sample

Payment PI1234567-FXTESTDATA12 John Smith AWF International Ltd - AC002233444 FXTESTDATA12 Expenses Claim 123/124

Payment PI0123456-FXTESTDATA12 John Doe AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 789/790/791

Payment PI3467890-FXTESTDATA12 Long John Silver AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 456/455/456/457

Payment PI2345678-FXTESTDATA12 Vini De Le Vinci AWF International Ltd - AC002233444 FXTESTDATA12 Expenses Claim 130

I can't assume that the PI reference or FX reference will stay the same length hence trying to use Instr() to get the positions.

And which portion of those strings are you wanting to extract?

 

[gemma-the-husky]

@Minty

so you have FX twice in each string.
What data are you trying to recover in the above examples?

 

[Minty]

Before you chaps waste any more effort on this, I do have a working solution, just not using the nested instr(), which appears to be something wrong with the db file.

I have extracted the First word (the transaction type), the PI reference, the FX reference, All the text after the second copy of the FX reference, and finally the name in the middle.

I achieved this using the small and very simple and hastily written function below to locate the beginning and end of the appropriate words, combined with the Mid() function.

Function fnWordStart (sString As String, sSearch As String, Optional iOccur As Long = 1) as Long

    Dim iCount  As Long
    Dim iPos    As Long
    
    iCount = 0
    iPos = 1
    While iCount < iOccur
        iPos = InStr(iPos, sString, sSearch)
        iCount = iCount + 1
        If iCount < iOccur Then iPos = iPos + 1
    Wend
    fnWordStart = iPos

End Function

I will add change the data types to accommodate null values, and add some error checking, but was getting a bit annoyed at the fact the nested Instr() was giving me errors when it shouldn't have been.

 

[arnelgp]

I achieved this using the small and very simple and hastily written function

add validation to check if ipos=0, in the case that the word being sought is not found.

 

[Minty]

Okay, this is really bugging me now.

I can run the self-same query on the same table in another older database and not get the error.

I have attached the copy of the DB that is giving me the problem.

 

[Minty]

Just as a bump - the sample database was created from scratch with just one sample data table and 1 query.

What's even more interesting is that if I copy the database that this did work in, remove all the other objects then compact and repair it - it then breaks. It goes from picture two to picture one in the post above.

I've just tried an office repair, no dice.
Am now trying a full online repair.

 

[Minty]

Bad form replying to your own thread but hey ho...
I've done a full Office reinstall and a reboot.

Further analysis brings me to the conclusion that something somewhere is properly broken.
If I move the exact thing that doesn't work in the query, into a function and call that from the query - it works.

This function

Option Compare Database
Option Explicit

Public Function fnFindStr(sString) As Long

    fnFindStr = InStr(InStr(1, sString, "FX"), sString, " ")

End Function

Used in this query

SELECT InStr(1,[Description_1],"FX") AS TheStartOfFX, InStr(InStr(1,[Description_1],"FX"),[Description_1]," ") AS StartOfTheFirstWordAfterFX, fnFindStr ([description_1]) AS UsingTheFunction
FROM Table_1;

You may notice in desperation I changed the field name to Description_1 just in case Description is possibly a reserved word...
Can someone humour me and create the same data and query and show me it working. Sample DB included.
Environment is
O365
64-Bit
Win 10

 

[Isaac]

I downloaded the attached Empty_Working.accdb and tried to run the query. Each time I double clicked on the query to open it, the database 'thought' for a few moments, then immediately closed/shut down. This happened 4 times in succession.

 

[NauticalGent]

Okay, this is really bugging me now.

I got the same.... MSO (16.0.13901.20276) 64 bit

 

[Minty]

I downloaded the attached Empty_Working.accdb and tried to run the query. Each time I double clicked on the query to open it, the database 'thought' for a few moments, then immediately closed/shut down. This happened 4 times in succession.

@Isaac Thank you for looking.

If you have time, can you cut and paste the data into a new blank database table and then recreate the query, do you get the same error?
I've attached the version with the function for reference if you want a source of the code and data without finding it here.

 

[Minty]

I got the same.... MSO (16.0.13901.20276) 64 bit

I'm using Microsoft® Access® for Microsoft 365 MSO (16.0.13929.20222) 64-bit

I'm going to recreate the "working version" DB as I have a backup and post that to see if anyone else doing a compact and repair then breaks it.

 

[NauticalGent]

Just to muddy the waters....I tested your nested Instr in the immediate window:

 

[Minty]

Just to muddy the waters....I tested your nested Instr in the immediate window:

View attachment 91389

Ditto ; works in the immediate window , not in a query in my new databases.

Immediate window output;
strInput = "Payment PI0123456-FXTESTDATA12 John Doe AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 789/790/791"
?instr(Instr(1,strInput,"fx"),strInput," ")
 31

 

[NauticalGent]

On a whim, I did a similar experiment with nested Replace() and got the same error. It appears you may have found a bug.

 

[gemma-the-husky]

I will have a look, but I don't want to mess around with your database.

given this sample string,
"Payment PI0123456-FXTESTDATA12 John Doe AWF International Ltd - AC002233444 FXTESTDATA12 Expenses 789/790/791"

please just clarify what you want to return. Note that FX is in the string twice.

 

[Minty]

Hi Dave, @gemma-the-husky

I need to separate out a number of the data points as follows.

Payment - First word String
PI0123456
FXTESTDATA12
John Doe
AC002233444
Expenses 789/790/791

Whilst currently the PI and FX strings (and others) are a fixed length in all the sample data I have seen, I can't rely on that long term.
I can guarantee the order and that they are separated by spaces or a "-".
In order to make this functional if the string lengths change, I want to determine the start and length of the strings to use in the Mid() function to extract them.

By passing the string into a function (shown in previous post), I can return the positions of the next space based on a search string, but it is doing exactly what the nested Instr() function should be doing in the query.

I know that there is something very wrong somewhere, as the nested Instr() works perfectly in a query in an older DB I created ages ago.
If I compact and repair that database I then get the error.

So to test this out, simply put that string into a table called table_1, with a single field called description_1.
Then run the following query

SELECT InStr(1,[Description_1],"FX") AS TheStartOfFX, InStr(InStr(1,[Description_1],"FX"), [Description_1], " ") AS StartOfTheFirstWordAfterTheFirstFX
FROM Table_1;

I get this

 

[arnelgp]

Just use the function. It wont work on double instr as everyone else experiences.

 

[Minty]

Just use the function. It wont work on double instr as everyone else experiences.

But it does and should work. That's the point.

I can nest Instr() in VBA, in the immediate window, and even in a controls source object.
And in a query in some databases. But not a new database.
I know I have used this technique many many times before.

Attached is a database where the query works.
Can you explain why compacting and repairing this database then stops it working?
(At least it breaks on my version of windows and office)

 

[theDBguy]

Attached is a database where the query works.
Can you explain why compacting and repairing this database then stops it working?
(At least it breaks on my version of windows and office)

Hi @Minty. I downloaded your file and opened the query. As expected, it works. I then did a C&R and opened the query again. In my case, it still worked!

I'm using Access Pro Plus 2016, Version 2103 (Build 13091.20462 Microsoft Store) on a Win 10 Home, Version 2004 (Build 19041.928)

Cheers!