Hale, Jim
jim.hale at fleetpride.com
Wed Mar 12 15:56:00 CST 2003
Page 727 of the ADH has code that may solve your problem. HTH Jim Hale -----Original Message----- From: John Clark [mailto:John.Clark at niagaracounty.com] Sent: Wednesday, March 12, 2003 3:23 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Painted into a corner I was just reading something in the MS knowledgebase, right before I read this Email, and I was thinking, "Don't tell me..." And, now you are giving me confirmation..."you can't get there from here." I entered in the names in the properties and it worked like a charm...again I learn something. The whole point of going about it the way I did though(i.e. dynamically), was to allow for flexibility. Therefore, typing in the names, severely defeats the purpose. Yesterday, I had come up with a way of getting the same information into the report, only it was in a different format. I did this by using three separate queries, and three sub-reports. I am going ahead with that, and I added a button on my reports menu that runs the query, and the can print that if they need to. I might just take it out, after I let them see it. Thank you for your help! John W Clark >>> BBarabash at tappeconstruction.com 03/12/03 03:07PM >>> 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 _______________________________________________ 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