crosstab query filtered by form

[mj]

hi. i have a report that has a crosstab query as a source.
i was hoping to filter the report by form buy i'm getting
an error saying that i can't do so. is there a way to
filter crosstab queries by form? thanks!

 

[Duane Hookom]

Open your crosstab in design view and menu select Query|Parameters and enter
your parameters and data types like:
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

 

[mj]

Thanks!!!

-----Original Message-----
Open your crosstab in design view and menu select Query|Parameters and enter
your parameters and data types like:
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
MS Access MVP
--




.

 

[mj]

hmm, now i get a message that says the microsoft jet
database engine does not recognize " as a valid field name
or expression. The parameter I'm filtering on is [Region]=
[Forms]![frmSalesGoalsParticipantsByRegion]![Region] where
I put[Forms]![frmSalesGoalsParticipantsByRegion]![Region]
in the parameters table and called it text. I then have a
column in my query for Region and the criteria is =[Forms]!
[frmSalesGoalsParticipantsByRegion]![Region]. Any
thoughts? Thanks!

 

[mj]

by the way, the query runs find using your suggestion but
when i run a report based on the query, that's when i get
the error...

 

[Duane Hookom]

Do you mind sharing your SQL view?

--
Duane Hookom
MS Access MVP
--

hmm, now i get a message that says the microsoft jet
database engine does not recognize " as a valid field name
or expression. The parameter I'm filtering on is [Region]=
[Forms]![frmSalesGoalsParticipantsByRegion]![Region] where
I put[Forms]![frmSalesGoalsParticipantsByRegion]![Region]
in the parameters table and called it text. I then have a
column in my query for Region and the criteria is =[Forms]!
[frmSalesGoalsParticipantsByRegion]![Region]. Any
thoughts? Thanks!

-----Original Message-----
Open your crosstab in design view and menu select Query|Parameters and enter
your parameters and data types like:
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
MS Access MVP
--




.

 

[mj]

It's ugly, but here it is...thanks for the help.

PARAMETERS [Forms]![frmSalesGoalsParticipantsByRegion]!
[Region] Value;
TRANSFORM Sum(tblSalesGoals.SalesGoal) AS SumOfSalesGoal
SELECT tblSalesGoals.Division, tblSalesGoals.Region,
tblSalesGoals.Territory,
SalesEndoSqlUser_accounts.keyAccount,
tblSalesGoals.SAPNumber, tblSalesGoals.CustomerName, Sum
(tblSalesGoals.SalesGoal) AS TotalGoal
FROM (SalesEndoSqlUser_ProductCategories LEFT JOIN
tblSalesGoals ON
SalesEndoSqlUser_ProductCategories.ProductLevelCustom =
tblSalesGoals.Product) LEFT JOIN SalesEndoSqlUser_accounts
ON tblSalesGoals.SAPNumber =
SalesEndoSqlUser_accounts.account_id
WHERE (((SalesEndoSqlUser_accounts.keyAccount)=1) AND
((tblSalesGoals.Region)=[Forms]!
[frmSalesGoalsParticipantsByRegion]![Region]))
GROUP BY tblSalesGoals.Division, tblSalesGoals.Region,
tblSalesGoals.Territory,
SalesEndoSqlUser_accounts.keyAccount,
tblSalesGoals.SAPNumber, tblSalesGoals.CustomerName
ORDER BY tblSalesGoals.Division, tblSalesGoals.Region,
tblSalesGoals.Territory, tblSalesGoals.CustomerName,
SalesEndoSqlUser_ProductCategories.ProductLevelCustom
PIVOT
SalesEndoSqlUser_ProductCategories.ProductLevelCustom;

-----Original Message-----
Do you mind sharing your SQL view?

--
Duane Hookom
MS Access MVP
--

hmm, now i get a message that says the microsoft jet
database engine does not recognize " as a valid field name
or expression. The parameter I'm filtering on is [Region]=
[Forms]![frmSalesGoalsParticipantsByRegion]![Region] where
I put[Forms]![frmSalesGoalsParticipantsByRegion]! [Region]
in the parameters table and called it text. I then have a
column in my query for Region and the criteria is = [Forms]!
[frmSalesGoalsParticipantsByRegion]![Region]. Any
thoughts? Thanks!

-----Original Message-----
Open your crosstab in design view and menu select Query|Parameters and enter
your parameters and data types like:
Forms!frmDates!txtStart Date/Time
Forms!frmDates!txtEnd Date/Time

--
Duane Hookom
MS Access MVP
--

hi. i have a report that has a crosstab query as a source.
i was hoping to filter the report by form buy i'm getting
an error saying that i can't do so. is there a way to
filter crosstab queries by form? thanks!


.

.

 

[Duane Hookom]

You probably have something in your report that doesn't match your query. I
can't see your report so I don't know what it is.

 

[mj]

ahh, I see. I have products listed across the top of the
report, our company's regions down the side. Problem is
that some of the regions don't sell all of the products,
so when I filter by a region that doesn't sell all the
products, the report will look for a product in the query
and can't find it. hmm. I guess i have to figure out some
way to include all products in the query even if they're
not sold...

 

[Duane Hookom]

One of the crosstab reports at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4 does this.

 

[mj]

I think I understand. It looks like this author uses a
placeholder "~" so that something is stored under each
column heading which, in my case, would be under the
products listed across the top of my report. Then, the
author uses the GetPart function to extract the relevant
data point and ignore the placeholder "~". If I'm way off,
please let me know. Thanks for the help on this. I greatly
appreciate it!

 

[Duane Hookom]

Actually, I created all the report demos when I had worked for that company.
The particular report I thought would work for you was in the Crosstab.mdb
file. There is a report with multiple dynamic column headings.