Description
When using the InStr function in the WHERE clause of a query or when filtering a query with an InStr function manually in Datasheet view, it can sometimes crash Access. This is present in version 2403 builds 17425.20146 and 17425.20176 (64 bit). The first affected build was rolled out to the Current Channel (CC) starting on March 29, 2024.
There is a forum discussion about this problem on Microsoft Answers.
Cause
Bug - The problem actually occurs when one of these functions is used in an expression in a query: InStr, InStrRev, Replace, and the expression is a part of a complex expression.
If you explicitly specify a final argument to the function of either 1 (which is vbTextCompare) or an LCID (e.g. 1033 for US English), then the problem will not occur. Note that if you do this, you also have to specify the first argument, which is the starting position.
The problem also does not occur if your database sort order is set to “General – Legacy”. This is why compact & repair can have an effect, since compact and repair will change the compacted database setting to match the current setting under General for New database sort order.
Status
Microsoft has located the problem and advised that this will be fixed in Version 2404, which will be available at the end of April.
Workaround 1
If you are in the Current Channel and use 64-bit Office, then try not to install version 2403 build 17425.20146 or roll back to an older version like 2402 build 17328.20184 if you have already done so.
Workaround 2
You could (possibly only temporarily) change the update channel. In the slower Monthly Enterprise Channel, the problem does not exist.
Workaround 3
If you can modify your query to include the first and fourth arguments of InStr to specify the comparison method, this should fix your issue.
Workaround 4
You could change the default sort order of your database by using Options - General - New database sort order to "General-Legacy", then compact and repair the problem database.