[AccessD] Painted into a corner

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



More information about the AccessD mailing list