crosstab query filtered by form

  • Thread starter Thread starter mj
  • Start date Start date
M

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!
 
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
 
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
--




.
Click to expand...
 
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!
 
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...
 
Do you mind sharing your SQL view?

--
Duane Hookom
MS Access MVP
--

mj said:
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
--




.
Click to expand...
Click to expand...
 
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!


.
Click to expand...
Click to expand...


.
Click to expand...
 
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.
 
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...
 
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!
 
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.
 
Back
Top