R
Randall Arnold
I'm having to filter records in an MS Chart (located in a report) based on
user-selected criteria. Unfortunately, the Chart object does not support
filters so I'm having to do this with SQL in the Chart's RowSource property.
The following string works as is (using % as a wildcard per SQL convention):
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '%' GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
The string above returns all records, sorted exactly as I want them. The
problem arises when I replace '%' with the value of the filtering control.
The control is not evaluated; instead, the NAME of the control rather than
its property winds up in the runtime SQL string.
I could have sworn I've made this work in the past but nothing I've tried
has worked, nor can I find a solution in the many texts I have or on the
Internet. I'm sure this is simple, but it's making me nuts!
Help?
user-selected criteria. Unfortunately, the Chart object does not support
filters so I'm having to do this with SQL in the Chart's RowSource property.
The following string works as is (using % as a wildcard per SQL convention):
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '%' GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
The string above returns all records, sorted exactly as I want them. The
problem arises when I replace '%' with the value of the filtering control.
The control is not evaluated; instead, the NAME of the control rather than
its property winds up in the runtime SQL string.
I could have sworn I've made this work in the past but nothing I've tried
has worked, nor can I find a solution in the many texts I have or on the
Internet. I'm sure this is simple, but it's making me nuts!
Help?