top of page
Writer's pictureMaria Barnes

InStr function in the WHERE clause of a query crashes Access (Fixed)

Updated: May 3



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

The problem gets fixed with Version 2404 published on May 1 in the Current Channel.


If you don't get the fixing build automatically you can pull it by choosing Update Now in File – Account. Until you install the fix, there are several workarounds:


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.

427 views2 comments

Recent Posts

See All

2 Comments


Gontran Harvey
Gontran Harvey
Apr 11

In normal circonstances I would advise to never filter a formated field on the left side of the where clause. But thanks for the tip about this bug.

Like

Trevor Lawson
Trevor Lawson
Apr 11

Try adding in the final optional compare parameter. It appears that it isn't as optional as MS would have you believe... I found this out to my cost and wasted hours trying to fathom out what was going on, discussed here: https://www.access-programmers.co.uk/forums/threads/nested-instr-issues.317649/

Like
bottom of page