sql - Displaying a Crosstab Query with a Parameter as Subform Source Object -

sql - Displaying a Crosstab Query with a Parameter as Subform Source Object -

ok access 2010 here.

i have crosstab, lets phone call "testcrosstab," has parameter access form command in query uses reference. looks this:

parameters [forms]![testform]![textdayspast] short; transform max(val) maxvalue select col criteria (select [date], 'crosstab column name 0' col, [query column name 0] val testquery union select [date], 'crosstab column name 1' col, [query column name 1] val testquery union '... etc ... ) grouping col pivot [date];

it's source query, seen above, called "testquery," , looks this:

select sourcedatabase.date, count(*) [query column name 0], sum(iif(stuff=stuff , stuff=stuff,1,0)) [query column name 1], '... etc ... sourcedatabase inner bring together seconddatabase on sourcedatabase.id = seconddatabase.id (((sourcedatabase.date) between date() , date()-forms!testform!textdayspast)) grouping sourcedatabase.date;

they both work great when user enters number form command "textdayspast" , manualy run queries in "pop-up."

what trying attach crosstab above source object of subform purely display purposes. in vba, after user inputs number "textdayspast," line, among others, called attach crosstab subform:

me.subformdisplay.sourceobject = "query.testcrosstab" me.subformdisplay.requery

upon entering number "textdayspast" , executing query command button, form appears work correctly (no errors) except crosstab never displays. if alter source query static "where," crosstab lose it's need "parameters" , displays subform source object. in using "parameters" not allow crosstab displayed.

any ideas on how around this? combining source query , crosstab 1 big monster prepare this?

thank input!

you cannot refer cross tab parameter in subform, far know. however, can build query in vba, eliminating need parameter , update sql of cross tab query referenced subform.

ssql="transform sum(table1.anumber) sumofanumber " _ & "select table1.atext table1 " _ & "where table1.atext='" & me.txttext & "' " _ & "group table1.atext pivot table1.atext2" ''permanently alter sql myxtab currentdb.querydefs("myxtab").sql=ssql me.mysubformcontrolname.requery

sql ms-access crosstab