Brett Barabash
BBarabash at TappeConstruction.com
Wed Mar 12 14:06:00 CST 2003
Access is rejecting it as a report recordsource because cross-tab queries can have a dynamic number of columns, depending on the data. For example, if you don't have any records with an MA status, that column will disappear. To ensure that the columns always appear, regardless of whether or not there is supporting data, specify the column list as follows: -Open the query in design view -Right click on the gray header area of the query and select "Query Properties" -In the Column Headings field, type a column delimited list of the columns you want to show. For Example: 'ADC',"HR','MA' This will add an IN clause after the PIVOT statement, and the query will always have a fixed number of columns. -----Original Message----- From: John Clark [mailto:John.Clark at niagaracounty.com] Sent: Wednesday, March 12, 2003 1:30 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Painted into a corner Actually, I typed "[Forms]![PrintMenu]![txtStartDate]" and "[Forms]![PrintMenu]![txtEndDate]" into the "Query Parameters" box (two separate entries, of course), and I indicated that they were of the "Date/Time" type, and Voila! It worked. I now have a crosstab query that looks like: txtResult Total Of txtControlNum ADC HR MA MA ONLY NPA FS CASES W/NO ERRORS FOUND 102 28 27 23 24 DENIED / WITHDRAWN 104 24 27 26 27 GRANT REDUCTION 102 27 25 24 26 I was thinking, "How great! Now I can pump this into the report, and be finished w/it!" But, Nooooooo! I didn't forsee that I couldn't just use this as a datasource for a report. Am I missing something here?! Can't you use a crosstab query for a report? I know this can't be right, and therefore I am having a stupidity moment...to bad it seems to be lasting longer than you're average "fleeting moment!" John >>> CWortz at tea.state.tx.us 03/11/03 04:15PM >>> John, The Jet engine does not know about Access forms. You need to assign '[Forms]![PrintMenu]![txtStartDate]' to a variable and use the variable in the SQL. Charles Wortz Software Development Division Texas Education Agency 1701 N. Congress Ave Austin, TX 78701-1494 512-463-9493 CWortz at tea.state.tx.us -----Original Message----- From: John Clark [mailto:John.Clark at niagaracounty.com] Sent: Tuesday 2003 Mar 11 15:10 To: accessd at databaseadvisors.com Subject: RE: [AccessD] Painted into a corner I decided to give this a try because you make it sound so simple. After creating the query, I get an error, "The Microsoft Jet database engine does not recognize '[Forms]![PrintMenu]![txtStartDate]' as a valid field name or expression. (Error 3070) <snipped rest of message from John> _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com