Instr() function crashes MS Access after 4/4/2024 update to office 16.0.17425.20146

Anonymous
2024-04-05T16:32:38+00:00

After Office updated to version 16.0.17425.20146 my MS Access crashes when running very simple functions. I created a new database with one table and one query.

Table1.Text1
Zebra
x-ray
4x4
Posted to X
Next
axe
None
extreme
wax

This works:

SELECT Table1.Text1, InStr(1,[Text1],"x") AS Field2

FROM Table1;

Query1
Text1 Field2
Zebra 0
x-ray 1
4x4 2
Posted to X 11
Next 3
axe 2
None 0
extreme 2
wax 3

This crashes MS Access before returning any results:

SELECT Table1.Text1, InStr(1,[Text1],"x") AS Field2

FROM Table1

WHERE (((InStr(1,[Text1],"x"))>0));

I tried compact/repair. Tried recreating the database from scratch. Rebooted my computer. I uninstalled and re-installed MS Office.

Any ideas??

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

54 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-04-09T16:48:38+00:00

    Hi dbMonkey,

    > Evidently, just using InStr in a query doesn't in itself cause the crash as the sample query can be opened. The issue appears to be related to using InStr in the WHERE clause, ...

    Okay, that's good input. But just to be clear--as I'm sure you likely know--one really doesn't want to include any functions in the WHERE clause unless there simply is no other choice. Certainly not in a production database with many thousands of records, because one is forcing a table scan. Lots of IO and CPU required for a production database with a table of any appreciable size. This holds true for JET databases (e.g. Access), SQL Server, Oracle, etc.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-04-09T16:34:24+00:00

    I got this issue in only one of my Access applications and in just the one query (amongst lots). It's the only one that used InStr. I haven't used Left or Right in any queries but have used Mid and those queries work fine.

    Evidently, just using InStr in a query doesn't in itself cause the crash as the sample query can be opened. The issue appears to be related to using InStr in the WHERE clause, which is the scenario with the query in my application and is effectively simulated by doing a filter on the column in the sample DB.

    If you run the following SQL in the sample DBs:

    SELECT ID, Desc
    FROM Table1
    WHERE InStr(Nz(Desc, "abc"), ".") > 0;

    Then this will cause the crash in the C&R DB (but runs OK in the pre-C&R DB).

    But running the following works fine in both DBs (no crash):

    SELECT ID, Desc

    FROM Table1
    WHERE Left(Nz(Desc, "abc"), 1) = "b";

    So it does seem to be an issue with InStr in a WHERE clause or filter specifically.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-04-09T15:50:58+00:00

    dbMonkey:

    > Just running C&R on this new tiny simple DB resulted in it starting to crash when doing the filter test, where it worked fine on the freshly created DB before C&R was run.

    Just curious....have you tried any other functions such as Left (or Left$), Mid (or Mid$), etc.?
    If this issue is reproducible for folks on the Access team at Microsoft, after a compact & repair as you stated, then I will say you've done very good documentation of the problem. The big question is if it is reproducible for others.

    I just tried your samples using my 64-bit MS Access, which is the Semi-Annual Enterprise Channel version [Version 2308 (Build 16731.20600 Click-to-Run). Both versions work fine, including after compact and repair. Interestingly, my compacted sizes are a bit smaller than the sizes you posted to your public OneDrive folder. Not sure if that means anything...

    Was this answer helpful?

    0 comments