Form control reference in crosstab query

T

tina

hi folks,

is there any way to use a reference to a form control in a crosstab query's
criteria, or in its' root query's criteria? i haven't had any luck doing so;
in a report based on a crosstab query, i've had to filter the data in the
OpenReport action - which is certainly done easily enough, just not my
preference. in a chart (in a report) with its' RowSource based on a crosstab
query (or directly on a TRANSFORM statement), i've had to resort to setting
the SQL property of the QueryDef of the root query in the report's Open
event, in order to get the current value of the form's control into the
criteria. this seems like a crazy solution to me, but the only other idea i
had was to dump the root query's recordset into a temp table each time i run
the report - and i liked that idea even less.

i've very little experience with crosstab queries, i'm afraid (and even less
with charts in Access) - so i'm wondering if there's some easier way to do
this that i just haven't figured out...?

and btw, i'm building the chart in an A2000 db using A2003, then converting
to A97 for "real world" use - and running into the same issue of
"The Microsoft Jet database engine does not recognize
'[Forms]![frm03DailyTallies]![cboEmp]' as a valid field name or expression."
regardless of which version of Access the database is in.

all comments and suggestions are appreciated.

tia, tina
 
J

John Vinson

is there any way to use a reference to a form control in a crosstab query's
criteria, or in its' root query's criteria?

Yes, by using the Query's Parameters collection to specify the form
reference as a paramter. It's always *allowed* to do so; for some
reason, it's obligatory for crosstab queries.

Right click in the background of the tables and select Parameters; put

[Forms]![NameOfForm]![NameOfControl]

in the parameter name column, and the datatype of the field being
searched in the other column.

John W. Vinson[MVP]
 
T

tina

<beats head against wall>
now why didn't i think of that in the first place? i usually specify a
parameter when referencing a date value from a form, but it never crossed my
mind...

thank you, thank you, John, you just made my life so much easier! :)


John Vinson said:
is there any way to use a reference to a form control in a crosstab query's
criteria, or in its' root query's criteria?

Yes, by using the Query's Parameters collection to specify the form
reference as a paramter. It's always *allowed* to do so; for some
reason, it's obligatory for crosstab queries.

Right click in the background of the tables and select Parameters; put

[Forms]![NameOfForm]![NameOfControl]

in the parameter name column, and the datatype of the field being
searched in the other column.

John W. Vinson[MVP]
 
J

John Vinson

<beats head against wall>
now why didn't i think of that in the first place? i usually specify a
parameter when referencing a date value from a form, but it never crossed my
mind...

thank you, thank you, John, you just made my life so much easier! :)

I'm delighted to be of help! It's a RARE day when you, of all people,
need my help - more often I'm gleaning your posts to see what tricks I
can learn!

John W. Vinson[MVP]
 
T

tina

I'm delighted to be of help! It's a RARE day when you, of all people,
need my help - more often I'm gleaning your posts to see what tricks I
can learn!

oh (lol)....not! you made a quick trip to the blarneystone for that one! ;)
i learn new things from you and the other MVPs, as well as the numerous
other skilled developers who answer posts in these NGs, all the time -
usually, i just quietly snatch those pearls from threads started by other
folks, rather than posting myself. <bg>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top