[AccessD] Painted into a corner

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



More information about the AccessD mailing list