referencing control value in RowSource SQL string

G

Guest

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?
 
G

Guest

If you just replace the name of the control, that is what you will get. See
the modification below for the syntax:
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' & GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
If [Responsibility] is a number field, remove the single quotes.
 
G

Guest

Sorry Randall, I have never used a Graph in a report, so I don't think I can
be of help in this situation. Good Luck.

Randall Arnold said:
If I could use a string variable as you show I'd have no problem.
Unfortunately, I get a nonsensical error (2445) if I try assigning the
RowSource property using a string (report Open event). I say nonsensical
because after the error message pops up and is closed, the code executes as
expected anyway. Error trapping has not worked. So for right now I'm
limited to building the SQL string in the property box for the Graph control,
and that's where my problems lie.

Thanks for your efforts anyway.

Randall

Klatuu said:
strSQL = "SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup
Issues Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' GROUP BY [Defect Description] ORDER BY
Count(*) desc, [Order Setup Issues Query].[Responsibility], [Order Setup
Issues Query].[Defect ID];"

The above should be correct. I don't know how you are using it, so I can't
give any more suggestions.

Randall Arnold said:
Well, thanks, but that was one of the many things I'd already tried, and in
response the control name shows up in the evaluated string, not the value.
Your recommendation is in fact what I was sure I'd successfully used
countless times before...

:

If you just replace the name of the control, that is what you will get. See
the modification below for the syntax:
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' & GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
If [Responsibility] is a number field, remove the single quotes.

:

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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

strSQL = "SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup
Issues Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' GROUP BY [Defect Description] ORDER BY
Count(*) desc, [Order Setup Issues Query].[Responsibility], [Order Setup
Issues Query].[Defect ID];"

The above should be correct. I don't know how you are using it, so I can't
give any more suggestions.

Randall Arnold said:
Well, thanks, but that was one of the many things I'd already tried, and in
response the control name shows up in the evaluated string, not the value.
Your recommendation is in fact what I was sure I'd successfully used
countless times before...

Klatuu said:
If you just replace the name of the control, that is what you will get. See
the modification below for the syntax:
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' & GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
If [Responsibility] is a number field, remove the single quotes.

Randall Arnold said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Well, thanks, but that was one of the many things I'd already tried, and in
response the control name shows up in the evaluated string, not the value.
Your recommendation is in fact what I was sure I'd successfully used
countless times before...

Klatuu said:
If you just replace the name of the control, that is what you will get. See
the modification below for the syntax:
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' & GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
If [Responsibility] is a number field, remove the single quotes.

Randall Arnold said:
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?
Click to expand...
Click to expand...
 
G

Guest

If I could use a string variable as you show I'd have no problem.
Unfortunately, I get a nonsensical error (2445) if I try assigning the
RowSource property using a string (report Open event). I say nonsensical
because after the error message pops up and is closed, the code executes as
expected anyway. Error trapping has not worked. So for right now I'm
limited to building the SQL string in the property box for the Graph control,
and that's where my problems lie.

Thanks for your efforts anyway.

Randall

Klatuu said:
strSQL = "SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup
Issues Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' GROUP BY [Defect Description] ORDER BY
Count(*) desc, [Order Setup Issues Query].[Responsibility], [Order Setup
Issues Query].[Defect ID];"

The above should be correct. I don't know how you are using it, so I can't
give any more suggestions.

Randall Arnold said:
Well, thanks, but that was one of the many things I'd already tried, and in
response the control name shows up in the evaluated string, not the value.
Your recommendation is in fact what I was sure I'd successfully used
countless times before...

Klatuu said:
If you just replace the name of the control, that is what you will get. See
the modification below for the syntax:
SELECT [Defect Description], Count(*) AS [Count] FROM [Order Setup Issues
Query] WHERE [Order Setup Issues Query].[Responsibility] LIKE '" &
forms!MyFormName!MyControlName & "' & GROUP BY
[Defect Description] ORDER BY Count(*) desc, [Order Setup Issues
Query].[Responsibility] desc, [Order Setup Issues Query].[Defect ID];
If [Responsibility] is a number field, remove the single quotes.

:

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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Retrieving Combobox RowSource 2
Running Total in SQL query not working as needed 5
How to get count of unique records in one-to-many query 3
Rowsource of unbound object 10
RowSource in VBA 10
RowSource with chart 6
SQL string in VBA 4
I'm not sure how to return the value using the SQL statement? 4

Top