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: Oldest
Most helpful Newest Oldest
  1. 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
  2. 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
  3. Anonymous
    2024-04-09T17:01:18+00:00

    Yes, Tom, I know it's inefficient. You're right to highlight it, thanks.

    This was a special case query to deal with a fiddly issue that arose with some data and is occasionally run as a data validation check.

    Was this answer helpful?

    0 comments