Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Form Parameter using a CrossTab query problem!! 2

Status
Not open for further replies.
lpbruce1

lpbruce1

MIS
Aug 2, 2004
15
0
0
US
I want to be able to run a query from a form however from the crosstab query when I attempt to use the 'Forms![frmProjectManagers]![ProdGrpID]!' from the build, it will not work on my crosstab query, I get the following error:

"The MS Jet database does not recognize '[Forms]![frmProjectManagers]![ProdGrpID]!' as a valid name or expression."

This method words fine on my other queries that are not crosstabs. Is there a limitation in Access or am I doing something wrong?

Any suggestion?

Lpbruce
 
Sort by date Sort by votes
You seem to have an extra ! at the end. Is it a typo?
[Forms]![frmProjectManagers]![ProdGrpID]
 
Upvote 0 Downvote
Sorry, that is a typo on my part. I meant to type:
[Forms]![frmProjectManagers]![ProdGrpID] in my posting.

lpbruce
 
Upvote 0 Downvote
Define the parameter in the crosstab query:
PARAMETERS [Forms]![frmProjectManagers]![ProdGrpID] Integer;
TRANSFORM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Remou - I am not quite sure what you mean by show you the SQL, do you want the sql from the query? if so here is the SQL view from Access (2002):
Code: 
<html>
    <body>
TRANSFORM Sum(dbo_Invoice.ExchangeRate*[Quantity]*[transactionprice]) AS InvoiceLineTotal
SELECT dbo_ProductGroup.ProductGroupID, dbo_ProductGroup.ProductGroupName
FROM ((((((dbo_ProductGroupCluster INNER JOIN dbo_ProductGroupCluster_ProductGroup ON dbo_ProductGroupCluster.ProductGroupClusterID = dbo_ProductGroupCluster_ProductGroup.ProductGroupClusterID) INNER JOIN dbo_ProductGroup ON dbo_ProductGroupCluster_ProductGroup.ProductGroupID = dbo_ProductGroup.ProductGroupID) INNER JOIN dbo_ProductGroup_Item ON dbo_ProductGroup.ProductGroupID = dbo_ProductGroup_Item.ProductGroupID) INNER JOIN dbo_Item ON dbo_ProductGroup_Item.ItemID = dbo_Item.ItemID) INNER JOIN dbo_InvoiceLineItem ON dbo_Item.ItemID = dbo_InvoiceLineItem.ItemID) INNER JOIN dbo_Invoice ON dbo_InvoiceLineItem.InvoiceID = dbo_Invoice.InvoiceID) INNER JOIN dbo_Order ON dbo_Invoice.OrderID = dbo_Order.OrderID
WHERE (((dbo_ProductGroup.ProductGroupID)=[Forms]![frmProductManagersReport]![cmbProdGrID]) AND ((dbo_Item.ItemConditionCode)=0))
GROUP BY dbo_ProductGroup.ProductGroupID, dbo_ProductGroup.ProductGroupName
PIVOT Year([InvoiceDate]);
 </body>
</html>

If not, then let me know what you need.

PHV

Where do I define the parameter:
In the query I have:

Field: ProductGroupID
Table: dbo_ProductGroupCluster_ProductGroup
Total: Where
Sort:
Criteria: [Forms]![frmProductManagersReport]![cmbProdGrID]

Sorry to sound so dumb, but I am fairly new to Access, so I really appreciate all your help.....!

lpbruce

 
Upvote 0 Downvote
menu Query -> Parameters

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Sorry lpbruce1, PHV fielded that, and PHV is much quicker than me, so I should have said never mind the SQL. :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Running query with parameter from Inputbox 2
Replies
8
Views
158
TrekBiker
TrekBiker
TrekBiker
  • Locked
  • Question
Use Crosstab query in subform
Replies
9
Views
113
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Form Data Not Updating
Replies
6
Views
124
dhookom
dhookom
TrekBiker
  • Locked
  • Question
Forcing Form Updates
Replies
2
Views
85
TrekBiker
TrekBiker
MattGreer
  • Locked
  • Question
Add Field Description to a Form 2
Replies
5
Views
104
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top