Character limit for WhereCondition in DoCmd.OpenReport

[dockeradz]

The character limit for the WhereConditionParameter of DoCmd.OpenReport has been 32,768 characters in the past as documented on https://learn.microsoft.com/en-us/of...cmd.openreport



I've noticed in Access 2021 the limit seems to be between 2045 and 2058, probably 2048.

Here is a sample whereclause (shortened and edited) and the openreport statement:

whereClause = [Machine Name] IN ('Machine1','Machine2') AND [Out Of Service] = False AND [Disposed]=False
DoCmd.OpenReport "SerialNumber", acViewPreview, , whereClause

IF the Whereclause is more than 2048 characters in Access 2021 I get the error:

Run-time error '7769':
The filter operation was canceled.  The filter would be too long.

MY questions are:
IS anyone else seeing this in Access 2021, is it just the version I'm on or something like that? I don't think this was the case in 2019.
Is there a better way to do this?

[Micron]

Have never tried to string together a filter long enough to see that. As for other ways, perhaps a named filter query, or construct the entire sql in vba and run from there, or use vba to edit the SqlDef property of a query and run the query.

[ArviLaanemets]

It looks like it is the list of machine names what is making the condition too long. Maybe you would consider (in case you don't have it jet) a table where all machines are listed. Then to get all registered machines included, the query will be

SELECT ... WHERE [Machine Name] IN (SELECT MachineName FROM tblMachines [WHERE ...]) AND [Out of Service] = False AND [Disposed] = False

In case the list of machines must be limited somewhy, I added an optional WHERE clause (in [] brackets).

Also, I did see you have in your original table fields "Out of Service" and "Disposed". You can consider having a field Satus instead - then your query will be like

SELECT ... WHERE [Machine Name] IN (SELECT MachineName FROM tblMachines [WHERE ...]) AND Status IN("Out of Service", "Disposed")

or in case you use numeric values for diffferent statuses, e.g. 8 for "Out of Service" and 9 for "Disposed"

SELECT ... WHERE [Machine Name] IN (SELECT MachineName FROM tblMachines [WHERE ...]) AND Status IN(8, 9)"

Edit: When fields [Out of Service] and [Disposed] in your original table mark statuses of machines, and the original table isn't the table of machines registry, then you have a problem with your DB structure!