I am working on a dynamic crosstab report in Access 2003. I've sorted out most of the typical issues re. setting field names and building the 'IN' clause of the SQL dynamically in the Report_Open routine - thanks to many helpful examples on these forums and elsewhere.
My question is about setting filters dynamically.
The way the report is set up, a filter based on user input is passed (along with other things) through the OpenArgs parameter. I would then like to insert this into the SQL recordsource as a WHERE clause. When I do this however, Access interprets any field names in the WHERE clause as parameters and prompts for a value. But when I paste the exact same SQL into a new query, it runs fine -- it does not prompt for parameter values! It only does this when running the SQL as a report recordsource. Is this a known bug in Access 2003?
I have tried many different syntax for the fields - with or without table names, with or without brackets, . or !, etc. -- makes no difference. I have tried putting the filter into a subquery in the FROM clause -- makes no difference.
I have seen the suggestion to use parameter queries for filtering crosstab reports. I could do this, but not sure how it would help -- don't you still need to put field names in a WHERE clause, e.g. WHERE [field] = [param] ? Also I would need to define many parameters for this report, it would kind of be a pain if I don't have to do it. And I don't want to go down that road if it's going to be a dead end anyway. Is there any way to simply get the crosstab SQL to run like it's supposed to for a report?
Any suggestions or pointers are greatly appreciated.
Eric
Update:
Now it is prompting for a parameter even without a WHERE clause, in fact without a trace of any parameter name anywhere in the SQL. I have completely changed the SQL, created a new report template, looked in every property of the report, still it's stuck on thinking there's some parameter in the recordsource SQL. I have even compacted/repaired the MDB. This is really driving me nuts! I repeat that THE QUERY WORKS FINE on it's own. So it's not some missing/misspelled field. It's only when used as the recordsource of a report that it prompts for a parameter.
In case it give you some hint to the problem, here is the original SQL (although, as I said, it doesn't seem to make a difference if I change it to some completely different crosstab query). "allProgReqSign" is a pretty basic join.
TRANSFORM Count(a.ProgReqIDX) AS CountOfProgReqIDX
SELECT a.TaskType
FROM allProgReqSign AS a
GROUP BY a.TaskType
PIVOT Mid(a.ReqAssnToName,InStr(1,IIf(IsNull(a.ReqAssnToName),"",a.ReqAssnToName)," ")+1);
When it gets passed into the report I added the filter based on a field in allProgReqSign called Status:
WHERE (([Status]="READY") OR ([Status]="REVIEWED") OR ([Status]="ASSIGNED") OR ([Status]="DATA SENT") OR ([Status]="APPROVED")) AND ((NOT ([Status]="NEW")) AND (Not ([Status]="DRAFT")) AND (NOT ([Status]="READY PENDING REVIEW")) AND (Not ([Status]="COMPLETED")))
That's when it started prompting for a parameter called "Status".
But now it prompts for "Status" even without the WHERE clause.
WTF??
My question is about setting filters dynamically.
The way the report is set up, a filter based on user input is passed (along with other things) through the OpenArgs parameter. I would then like to insert this into the SQL recordsource as a WHERE clause. When I do this however, Access interprets any field names in the WHERE clause as parameters and prompts for a value. But when I paste the exact same SQL into a new query, it runs fine -- it does not prompt for parameter values! It only does this when running the SQL as a report recordsource. Is this a known bug in Access 2003?
I have tried many different syntax for the fields - with or without table names, with or without brackets, . or !, etc. -- makes no difference. I have tried putting the filter into a subquery in the FROM clause -- makes no difference.
I have seen the suggestion to use parameter queries for filtering crosstab reports. I could do this, but not sure how it would help -- don't you still need to put field names in a WHERE clause, e.g. WHERE [field] = [param] ? Also I would need to define many parameters for this report, it would kind of be a pain if I don't have to do it. And I don't want to go down that road if it's going to be a dead end anyway. Is there any way to simply get the crosstab SQL to run like it's supposed to for a report?
Any suggestions or pointers are greatly appreciated.
Eric
Update:
Now it is prompting for a parameter even without a WHERE clause, in fact without a trace of any parameter name anywhere in the SQL. I have completely changed the SQL, created a new report template, looked in every property of the report, still it's stuck on thinking there's some parameter in the recordsource SQL. I have even compacted/repaired the MDB. This is really driving me nuts! I repeat that THE QUERY WORKS FINE on it's own. So it's not some missing/misspelled field. It's only when used as the recordsource of a report that it prompts for a parameter.
In case it give you some hint to the problem, here is the original SQL (although, as I said, it doesn't seem to make a difference if I change it to some completely different crosstab query). "allProgReqSign" is a pretty basic join.
TRANSFORM Count(a.ProgReqIDX) AS CountOfProgReqIDX
SELECT a.TaskType
FROM allProgReqSign AS a
GROUP BY a.TaskType
PIVOT Mid(a.ReqAssnToName,InStr(1,IIf(IsNull(a.ReqAssnToName),"",a.ReqAssnToName)," ")+1);
When it gets passed into the report I added the filter based on a field in allProgReqSign called Status:
WHERE (([Status]="READY") OR ([Status]="REVIEWED") OR ([Status]="ASSIGNED") OR ([Status]="DATA SENT") OR ([Status]="APPROVED")) AND ((NOT ([Status]="NEW")) AND (Not ([Status]="DRAFT")) AND (NOT ([Status]="READY PENDING REVIEW")) AND (Not ([Status]="COMPLETED")))
That's when it started prompting for a parameter called "Status".
But now it prompts for "Status" even without the WHERE clause.
WTF??
Last edited:
