Variable for crosstab query
Access:
Need my query to ask for input. I would like for this to pop open a box where I can input a variable for tbl_Tasks.New_Crafts.
TRANSFORM Count(tbl_Tasks.New_Crafts
SELECT tbl_Tasks.TASKSDescription
FROM tbl_Tasks
WHERE (((tbl_Tasks.New_Crafts)="
GROUP BY tbl_Tasks.TASKSDescription
PIVOT tbl_Tasks.New_Crafts;
Need my query to ask for input. I would like for this to pop open a box where I can input a variable for tbl_Tasks.New_Crafts.
TRANSFORM Count(tbl_Tasks.New_Crafts
SELECT tbl_Tasks.TASKSDescription
FROM tbl_Tasks
WHERE (((tbl_Tasks.New_Crafts)="
GROUP BY tbl_Tasks.TASKSDescription
PIVOT tbl_Tasks.New_Crafts;
ASKER
I tried that but I get an error that says 'The Microsoft Access database engine does not recognize '[Enter New Crafts ID:]' as a valid field name or expression. ?????
ASKER
I can put something there and the query runs but I can't get the parameter to work.
Hm. New_Crafts is a text field, yes? and e is one of the entries in it? Did you leave the quotation marks in the query? Can you post a DB containing the table and the query so we can take a look at it?
ASKER
New_Crafts is a text field, yes? yes
e is one of the entries in it? yes
Did you leave the quotation marks in the query? yes
e is one of the entries in it? yes
Did you leave the quotation marks in the query? yes
Remove the quotation marks, and try again. :)
ASKER
Thanks for posting the DB. :)
You did exactly what I told you; turns out that crosstab queries require an additional step for user prompting.
Add PARAMETERS New_Crafts Text ( 255 ); at the top of the sql text of your query:
and you should get a prompt to enter the criteria when you run the query.
Or, to use the GUI: open the query in Design view, and in the Show/Hide section of the Design tab, click the Parameters button.
The Query Parameters window will open. Type the name of the field you want to use the criteria against, and hit tab. The Data Type should auto populate (if it doesn't, check the spelling of the field name).
Click OK to close the Parameter window.
Thanks; I didn't know this about crosstab queries. :)
You did exactly what I told you; turns out that crosstab queries require an additional step for user prompting.
Add PARAMETERS New_Crafts Text ( 255 ); at the top of the sql text of your query:
and you should get a prompt to enter the criteria when you run the query.Or, to use the GUI: open the query in Design view, and in the Show/Hide section of the Design tab, click the Parameters button.
The Query Parameters window will open. Type the name of the field you want to use the criteria against, and hit tab. The Data Type should auto populate (if it doesn't, check the spelling of the field name).
Click OK to close the Parameter window.
Thanks; I didn't know this about crosstab queries. :)
ASKER
I tried that but it doesn't seem to be working correctly??
It's working for me, and I can enter different letters as criteria...
CrossTabQueryIssue.accdb
CrossTabQueryIssue.accdb
ASKER
take the 'e' out of the query and try to run it again using e in the parameter box - you will get a different return.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's what I need. Thanks. Question - why would the crosstab not work with a parameter in the same way?
ASKER
Very helpful. Thanks for everything.
I have to confess that I don't know; I don't use crosstab queries much. Perhaps one of the serious Access gods could shed some light? Gustav? Dale?
The brackets surround a message that will be displayed to the user when the query runs. The user will get a dialog box prompting the entry of the criteria.