[AccessD] Painted into a corner

John,
This is an interesting wrinkle indeed!  For some reason, crosstab queries
won't accept form control references as parameters unless you declare them
as parameters.  All other queries work fine.  So, to get it to work, you
need to declare 2 parameters:

Parameter Name: [Forms]![PrintMenu]![txtStartDate]
Datatype: Date/Time

Parameter Name: [Forms]![PrintMenu]![txtEndDate]
Datatype: Date/Time

I just tested this out, and it works great!


-----Original Message-----
From: John Clark [mailto:John.Clark at niagaracounty.com]
Sent: Tuesday, March 11, 2003 3:10 PM
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)

I looked up the error and it says:

Possible causes with Microsoft Access: 

	· You have a parameter in a crosstab query or in a query that a
crosstab query or chart is based 	on, and the parameter data type
isn't explicitly specified in the Query Parameters dialog box. To
	solve the problem:

		· In the query that contains the parameter, specify the
parameter and its data type in 		the Query Parameters dialog box.
And;

		·Set the ColumnHeadings property for the query that
contains the parameter.

I didn't see a "Query Parameters" dialog box, so I then looked up
"PARAMETERS Declaration" in the Access help...this is A97 BTW...and it
gave me sample code:

	PARAMETERS [Enter a Last Name:] Text;
	SELECT *
	FROM Employees
	WHERE LastName = [Enter a Last Name:];

and this looks simple enough...for a single, user input,
parameter...but I have the following line:

	>=[Forms]![PrintMenu]![txtStartDate] And
<=[Forms]![PrintMenu]![txtEndDate]

So, to throw a wrench in the works, I not only have two parameters, but
they are located on a form. It may sound stupid, but what is actually my
parameter(s) here? I now know where the "Query Parameters" is, but
either way, I don't understand what exactly I am declaring.

Thanks for the help though...I actually see the cross-tab idea a little
clearer now...not there yet, but it will.

John  W Clark

>>> BBarabash at tappeconstruction.com 03/11/03 02:28PM >>>
It sounds like you need a cross-tab result on your report, that looks
something like this:

Status                     ADC  HR  MA
======================================	
CASES W/NO ERRORS FOUND     ##  ##  ##
DENIED/WITHDRAWN            ##  ##  ##
GRANT REDUCTION             ##  ##  ##

Cross-tab queries are fairly easy to implement, but a little tricky to
describe.  I could give you the boring SQL to do it, but it is WAY
easier to
use the Query grid to design them.  They consist of 3 elements: row
heading,
column heading, and value.  In this case, the status is the row
heading, the
ADC/HR/MA specification is the column heading, and the count is the
value.

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com