Storing query criteria captured in a form

  • Thread starter Thread starter NoviceAccessUser-Melanie
  • Start date Start date
N

NoviceAccessUser-Melanie

mI have a Select query where I've added the abiity for the user to select
the criteria from a Form. The expression in my criteria line is " Like
[Forms]![frmSelectSponsor]![txtSponsor] " and another cirteria for another
field is " In ("Actual-HTC","Actual-StateCredit")".

I take the results of this Select query and run a Crosstab query. I now get
an error message saying the the Microsoft Jet Engine does not recognize the
field...
Like [Forms]![frmSelectSponsor]![txtSponsor] ".

At first I tried to build a workaround by creating a Make Table query out of
the result of the first Select query. Then creating another Select query out
of the new Table. Then proceed to do the Crosstab. This seems to work but
just a very lengthy workaround.

I am wondering if there is another way to store the results of the query
criteria and of the query itself so I can just take that and run the Crosstab
query against it. It sounds so simple but I can't seem to see what I'm
missing. Can somebody lend me a hand please?
 
First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
-- [Forms]![frmSelectSponsor]![txtSponsor]
Select the data type of the parameter in column 2
-- Text

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
What about the second parameter which is " In
("Actual-HTC","Actual-StateCredit") " where I want the values of the IND
field to contain the words "Actual-HTC" or "Actual-StateCredit"?

John Spencer (MVP) said:
First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
-- [Forms]![frmSelectSponsor]![txtSponsor]
Select the data type of the parameter in column 2
-- Text

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

NoviceAccessUser-Melanie said:
mI have a Select query where I've added the abiity for the user to select
the criteria from a Form. The expression in my criteria line is " Like
[Forms]![frmSelectSponsor]![txtSponsor] " and another cirteria for another
field is " In ("Actual-HTC","Actual-StateCredit")".

I take the results of this Select query and run a Crosstab query. I now get
an error message saying the the Microsoft Jet Engine does not recognize the
field...
Like [Forms]![frmSelectSponsor]![txtSponsor] ".

At first I tried to build a workaround by creating a Make Table query out of
the result of the first Select query. Then creating another Select query out
of the new Table. Then proceed to do the Crosstab. This seems to work but
just a very lengthy workaround.

I am wondering if there is another way to store the results of the query
criteria and of the query itself so I can just take that and run the Crosstab
query against it. It sounds so simple but I can't seem to see what I'm
missing. Can somebody lend me a hand please?
Click to expand...
Click to expand...
 
That is NOT a parameter, that is criteria using literal values.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


NoviceAccessUser-Melanie said:
What about the second parameter which is " In
("Actual-HTC","Actual-StateCredit") " where I want the values of the IND
field to contain the words "Actual-HTC" or "Actual-StateCredit"?

John Spencer (MVP) said:
First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
-- [Forms]![frmSelectSponsor]![txtSponsor]
Select the data type of the parameter in column 2
-- Text

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

NoviceAccessUser-Melanie said:
mI have a Select query where I've added the abiity for the user to select
the criteria from a Form. The expression in my criteria line is " Like
[Forms]![frmSelectSponsor]![txtSponsor] " and another cirteria for another
field is " In ("Actual-HTC","Actual-StateCredit")".

I take the results of this Select query and run a Crosstab query. I now get
an error message saying the the Microsoft Jet Engine does not recognize the
field...
Like [Forms]![frmSelectSponsor]![txtSponsor] ".

At first I tried to build a workaround by creating a Make Table query out of
the result of the first Select query. Then creating another Select query out
of the new Table. Then proceed to do the Crosstab. This seems to work but
just a very lengthy workaround.

I am wondering if there is another way to store the results of the query
criteria and of the query itself so I can just take that and run the Crosstab
query against it. It sounds so simple but I can't seem to see what I'm
missing. Can somebody lend me a hand please?
Click to expand...
Click to expand...
Click to expand...
 
The error message went away but now if I take the results of the Crosstab
query a step further and create a Select query but I don't see the fields
when I use the query as my data source. I've tried adding the same parameter
to the Select query but when I create a report based on the new Select query,
the fields won't even display when the Query Wizard is run.
 
Back
Top