I have a report which is based on a crosstab query. My report's VBA code draws from an example that I used with success for a report with dynamic column headings. The VBA code for the report goes something like this:
'Create recordset for report.
Dim qdf AS QueryDef
Set dbsReport=Currentdb
Set qdf=dbsReport.QueryDefs("name of query"
Set rstReport=qdf.OpenRecordset()
This runs great for crosstab queries without parameters. However, when I put parameters into the crosstab query, I get an error #3061, too few parameters, expected 1. Based on what I read from other threads, I tried to copy the SQL statement from the query but can't get the syntax correct or something. Here is my SQL from the crosstab query with the parameter:
PARAMETER [forms]![nameform]![name] Text (255);
TRANSFORM Sum(GenClinical.[Number of Patients Encountered]) AS [SumOfNumber of Patients Encountered]
SELECT GenClinical.Department
FROM GenClinical INNER JOIN [Patient Data] ON GenClinical.ShiftID=[Patient Data].ShiftID
GROUP BY GenClinical.Department, GenClinical.Name
PIVOT [Patient Data].Agerange;
Can anyone help with this? I've searched several threads and have found several things to point to my problem being with inserting the SQL statement into the report, but I'm new to this VBA, as well as database, stuff and I'm not even sure how much of the above SQL should be in the code or what should be in quotes and what shouldn't. Thanks in advance!
'Create recordset for report.
Dim qdf AS QueryDef
Set dbsReport=Currentdb
Set qdf=dbsReport.QueryDefs("name of query"
Set rstReport=qdf.OpenRecordset()
This runs great for crosstab queries without parameters. However, when I put parameters into the crosstab query, I get an error #3061, too few parameters, expected 1. Based on what I read from other threads, I tried to copy the SQL statement from the query but can't get the syntax correct or something. Here is my SQL from the crosstab query with the parameter:
PARAMETER [forms]![nameform]![name] Text (255);
TRANSFORM Sum(GenClinical.[Number of Patients Encountered]) AS [SumOfNumber of Patients Encountered]
SELECT GenClinical.Department
FROM GenClinical INNER JOIN [Patient Data] ON GenClinical.ShiftID=[Patient Data].ShiftID
GROUP BY GenClinical.Department, GenClinical.Name
PIVOT [Patient Data].Agerange;
Can anyone help with this? I've searched several threads and have found several things to point to my problem being with inserting the SQL statement into the report, but I'm new to this VBA, as well as database, stuff and I'm not even sure how much of the above SQL should be in the code or what should be in quotes and what shouldn't. Thanks in advance!
