Question

Need help finding specific queries based on text strings in Prepackaged Access Reports.

Answer

The following may be helpful in trying to find specific queries based on a text string.

List queries that use the specified table:

SELECT MSysQueries.Name1 AS [Table Name], MSysObjects.Name AS [Queries that use the table]
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysQueries.Name1) Like [Table name (use * to truncate): ])
AND ((MSysQueries.Attribute)=5))
ORDER BY MSysObjects.Name;

List queries that display the specified field:

SELECT MSysQueries.Expression AS [Field name], MSysObjects.Name AS [Query that displays this field]
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysQueries.Attribute)=6) AND ((MSysQueries.Expression)
Like [Table name (use * to truncate): ]+'.'+[Field name (use * to truncate):]))
ORDER BY MSysQueries.Expression;

List queries that contain a certain string in a criterion:

SELECT MSysQueries.Expression AS Criterion, MSysObjects.Name AS [Queries that use this criterion]
FROM MSysQueries INNER JOIN MSysObjects ON MSysQueries.ObjectId = MSysObjects.Id
WHERE (((MSysQueries.Attribute)=8) AND
((MSysQueries.Expression) Like '*'+[Part of a criterion: ]+'*'))
ORDER BY MSysObjects.Name;

List queries with specified string in query name:

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((UCase([Name])) Like '*'+UCase([Part of a query name: ])+'*') AND ((MSysObjects.Flags)=0))
ORDER BY MSysObjects.Name;

Additional Information

See also: List Voyager Prepackaged Access Reports queries added or modified since a specific date

• Article last edited: 01-Jun-2021