Solved Nested Instr() Issues

[Minty]

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!

Really interesting - can you send me a copy of the C&R version please - I'll PM my email address rather than clogging up the forum.

 

[gemma-the-husky]

I think if you have this working, I won't do any work. However, given this string it would raise a number of thoughts about the structure of the string, mentioned below.

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

a) you have FX at 2 places, both formatted slightly differently.
What would happen if FX also appeared in, say the company name after John Doe ...

b) You are returning just John Doe from "John Doe AWF International Ltd" -
Are you actually getting the first 2 words, or something different?
What would happen if this was blank, or had only 1 word?

c) Is the dash before the Account Number always present?

It's just making sure that the string follows a consistent format.

 

[Minty]

I think if you have this working, I won't do any work. However, given this string it would raise a number of thoughts about the structure of the string, mentioned below.

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

a) you have FX at 2 places, both formatted slightly differently.
What would happen if FX also appeared in, say the company name after John Doe ...

b) You are returning just John Doe from "John Doe AWF International Ltd" -
Are you actually getting the first 2 words, or something different?
What would happen if this was blank, or had only 1 word?

c) Is the dash before the Account Number always present?

It's just making sure that the string follows a consistent format.

The data is a fictitious version of an extract from a FrontierPay transaction CSV export file.
This is the transaction description field, which needs parsing to identify various elements not available anywhere else in the export.

a) From what I can tell the FX string data part is duplicated identically in both places.
b) The source account name (The fictitious "AWF International Ltd") never changes in each line. The "John Doe" name can be any length and any number of words.
c) Yes it is.

I have no real difficulty in parsing the string data, but I do have difficulty with Access messing with a function not working when it should, and really frustratingly it appears to work fine on another identical system.

I'm in the throws of doing a proper Office removal and reinstall, as it appears to be my computer/system at fault.
My colleague's system does not break the version @theDBguy sent back to me earlier when he does C & R on it, so it is all pointing to some weird Office/Windows related fault on my laptop.

As it's Friday and nearly 5:00pm it's officially the weekend and after all the time I have wasted on this, it's time for a beer, a dog walk, and on-line Poker with some pals.

Thanks to everyone for their assistance and suggestions. I'll report back after the rebuild.

 

[NauticalGent]

it's time for a beer, a dog walk, and on-line Poker with some pals.

Now you're speaking my kinda language!

 

[Minty]

So I have removed Office and re-installed. Exactly the same problem.

One further piece of interesting grist to the mill, if I send the "broken" version to a colleague and he compacts and repairs it, it really does repair it, the error goes away and the correct expression result returns!

The only "non-standard" thing I have installed is MZ-Tools that I've been using for years...
And talk about timing, I have just been sent this from a colleague;

Access query function InStr, optional arguments now required?

For example, "select t.ID, t.Field1, Instr(t.Field1, "test") as Expr1 from Table1 as t". This does not work after the recent updates. Now I must rewrite the query as "select t.ID, t.Field1, Instr(1, t.Field1, "test", 1) as Expr1 from Table1 as t". The first argument is supposed to be optional and defaults to 1, but omitting it causes the application to crash. The last argument is also optional and defaults to the Option Compare setting, but omitting it causes an error. Note that this behavior is only for queries. VBA functions continue to work as documented.

Low and behold - if I add the final "OPTIONAL" parameter my problem disappears, even though it isn't a problem for other users of the same version I have.
What a pain, and what a massive waste of effort to get to the bottom of it. It appears to only affect my installation and whoever this was affecting.

 

[Minty]

Attached is the final version of the problem.
Broken Query returns #Error in the second column on my system. Fixed Query doesn't.

If you compact and repair the attached database I suspect both will work, if they don't already.

Link to the MSoft Q & A https://answers.microsoft.com/en-us...ents-now/3b761d3d-37f8-4995-84d8-1ac04e12daf4

 

[Isaac]

Just tried it and had the same thing happen to me as I did when I tried it on a few posts back earlier. I download the attached db, open it, enable content, double-click the Broken query to run it, and Access disappears - db is gone & closes.

Definitely something wonky!

Glad to hear you got it fixed, Minty. Frustrating process to go through but at least you offset the natural dismay of Monday, with solving a problem!

 

[NauticalGent]

Attached is the final version of the problem.
Broken Query returns #Error in the second column on my system. Fixed Query doesn't.

If you compact and repair the attached database I suspect both will work, if they don't already.

Link to the MSoft Q & A https://answers.microsoft.com/en-us...ents-now/3b761d3d-37f8-4995-84d8-1ac04e12daf4

Excellent follow-up Minty, thanks for seeing this through. If we do not have a Forum for these type of issues than it might be worthwhile to establish one. Might be a little overkill since MS already has one. Maybe a sticky that would give members and guests another resource.

Just a thought.

P.S. Looks like the beers and on-line poker was the right course of action!

 

[Minty]

P.S. Looks like the beers and on-line poker was the right course of action!

Hmmm, I was down for the first time in months, so I guess I was just having one of those weeks.
Still had a load of beer and laughs though

 

[gemma-the-husky]

Instr(1, t.Field1, "test") should work. The optional parameter at the end just determines whether the check is for instance case sensitive.
Instr(t.Field1, "test") should also work.

I just tested this, and for example, the following worked correctly and selected just 4 items in a query

Expr1: InStr([filename],"90045")
criteria >0

This is with office 365, 32 bit


I tried your version of Public Function fnfindstr(sString) As Long

I didn't get an error with brokenquery.
this errors if you pass a blank string, or a string that doesn't contain "FX" at all. Would that be the issue?

As a result, I changed the code to this to include some testing just in case.

Public Function fnfindstr(sString) As Long

    If Len(sString) = 0 Then
        fnfindstr = 0
        Exit Function
    End If
  
    If InStr(sString, "FX") = 0 Then
        fnfindstr = 0
        Exit Function
    End If
  
    fnfindstr = InStr(InStr(1, sString, "FX"), sString, " ")

End Function

 

[Minty]

@gemma-the-husky That would work, except the problem is when I'm using the inbuilt Instr() function in the query.

 

[gemma-the-husky]

@gemma-the-husky That would work, except the problem is when I'm using the inbuilt Instr() function in the query.

I edited my post, but your queries are both working for me.

I only get an error if I add a string that doesn't contain FX - eg just "payment"

 

[Minty]

I edited my post, but your queries are both working for me.

I only get an error if I add a string that doesn't contain FX - eg just "payment"

Thanks for looking. I had hard-coded the function just to prove to myself that the Instr() function worked outside of the query.

I'm not overly surprised it works for you, I have one colleague who can replicate the issue and another who can't, we are all on exactly the same Office and Windows versions.
I suspect it is an issue with some weird registry or another black hole setting.