crosstab query parameters

[Pete Rothery]

I need to use a crosstab query with a "parameter query" (user enters dates)
and understand that I must define the parameters in the crosstab query - but
can't get the bracketing right, I have tried most combinations and this is by
latest failure

PARAMETERS [Table!Timeweek!ClientID] Short, [Table!Timeweek!TimeUnits] Short;

What am I doing wrong?

Help really appreciated

pete

 

[Tom Wickerath]

Hi Pete,

The parameter should be exactly the same as you would enter into the
criteria line of the QBE grid. You likely would not use a parameter that
looked like this in a criteria line, right? [Table!Timeweek!ClientID]. I
usually just copy the parameter prompt from the criteria line, and paste it
into the Query > Parameters... dialog. Be careful that you do not get a
leading space inserted when you do the paste.

See paragraph # 15 of this article for an example of entering a Date criteria:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Pete Rothery]

Thanks Tom - it easy when you know - I wasn't even thinking the right way.
Your crosstab query tutorial is very good too - I bookmarked it for the next
time i get confused.

Pete

Hi Pete,

The parameter should be exactly the same as you would enter into the
criteria line of the QBE grid. You likely would not use a parameter that
looked like this in a criteria line, right? [Table!Timeweek!ClientID]. I
usually just copy the parameter prompt from the criteria line, and paste it
into the Query > Parameters... dialog. Be careful that you do not get a
leading space inserted when you do the paste.

See paragraph # 15 of this article for an example of entering a Date criteria:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I need to use a crosstab query with a "parameter query" (user enters dates)
and understand that I must define the parameters in the crosstab query - but
can't get the bracketing right, I have tried most combinations and this is by
latest failure

PARAMETERS [Table!Timeweek!ClientID] Short, [Table!Timeweek!TimeUnits] Short;

What am I doing wrong?

Help really appreciated

pete

 

[Tom Wickerath]

Hi Pete,

Glad I could help, and thank you for the nice compliment!


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[sg]

Tom -

I'm not sure if you'll see this since I'm replying to an old thread, but I
thought I would give it a shot.

I'm working on a crosstab query that has parameters. Your article that you
gave the link for helped me to realize that I needed to add the parameters to
the Query/Parameters as well as in the criteria row. The query runs fine now
but I have a problem with the report that is based on that query.

When I run the report, it asks me to fill in the parameters over and over
again, but doesn't get to the report (at least I wasn't patient enough to try
it for more than 3 times).

When I get to Design View of the report, all of the fields listed in the
Detail section show up as if they are not fields that are in the data source.


Any ideas as to what I can do to fix this problem? I appreciate any help
you (or anyone replying to this) can give!

Thanks.

Hi Pete,

The parameter should be exactly the same as you would enter into the
criteria line of the QBE grid. You likely would not use a parameter that
looked like this in a criteria line, right? [Table!Timeweek!ClientID]. I
usually just copy the parameter prompt from the criteria line, and paste it
into the Query > Parameters... dialog. Be careful that you do not get a
leading space inserted when you do the paste.

See paragraph # 15 of this article for an example of entering a Date criteria:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I need to use a crosstab query with a "parameter query" (user enters dates)
and understand that I must define the parameters in the crosstab query - but
can't get the bracketing right, I have tried most combinations and this is by
latest failure

PARAMETERS [Table!Timeweek!ClientID] Short, [Table!Timeweek!TimeUnits] Short;

What am I doing wrong?

Help really appreciated

pete

 

[John W. Vinson]

I'm working on a crosstab query that has parameters. Your article that you
gave the link for helped me to realize that I needed to add the parameters to
the Query/Parameters as well as in the criteria row. The query runs fine now
but I have a problem with the report that is based on that query.

When I run the report, it asks me to fill in the parameters over and over
again, but doesn't get to the report (at least I wasn't patient enough to try
it for more than 3 times).

When I get to Design View of the report, all of the fields listed in the
Detail section show up as if they are not fields that are in the data source.


Any ideas as to what I can do to fix this problem? I appreciate any help
you (or anyone replying to this) can give!

The simplest way is to create an unbound form (let's call it frmCrit) with
textboxes or other controls for the parameters. The parameters in the query
would be

=[Forms]![frmCrit]![controlname]

both in the Parameters section and the criteria.