Query parameter not working when expression points to form field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a cross tab query with a where clause. I want the where clause to get
it's value from an open form using the syntax.

[Forms]![Client]![SolutionSelection]

This works just fine with a non cross tab query. However the same
expression does not work with a cross tab.

Any thoughts as to why and if there is a workaround?


Crosstab Query
PARAMETERS tst Short;
TRANSFORM Max(PropertyValue.property_value) AS MaxOfproperty_value
SELECT Specification.spec_id, Specification.spec_name
FROM Specification INNER JOIN ((Property INNER JOIN PropertyValue ON
Property.prop_id = PropertyValue.property_id) INNER JOIN (PropertyGroup INNER
JOIN PropGrpRel ON PropertyGroup.propgroup_id = PropGrpRel.propgroup_id) ON
Property.prop_id = PropGrpRel.prop_id) ON Specification.spec_id =
PropertyValue.object_id
WHERE (((Specification.spec_type_id)=24) AND
((Specification.spec_owning_id)=[Forms]![Client]![SolutionSelection]))
GROUP BY Specification.spec_id, Specification.spec_name
PIVOT Property.prop_name;


Non Cross tab query
SELECT Specification.*, Specification.spec_owning_id
FROM Specification
WHERE (((Specification.spec_owning_id)=[Forms]![Client]![SolutionSelection]));
 
Brady,
did you try using two queries? The first one, the select query would
get the parameter from the form, then the second (the xtb) would build
the crosstab on the first query.
 
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
Select the data type of the parameter in column 2
 
When you say exact name of the parameter are you refering to the form field
like [Forms]![Client]![SolutionSelection] or the name of the field that is
being queryed. At any rate I tried both. If I use the
[Forms]![Client]![SolutionSelection] as the parameter name it simply prompts
me vs. pulling the value from the form. If I use spec_owner_id the name of
the column that has the where clause then I get invalid query.

John Spencer 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
Select the data type of the parameter in column 2


Brady said:
I have a cross tab query with a where clause. I want the where clause to
get
it's value from an open form using the syntax.

[Forms]![Client]![SolutionSelection]

This works just fine with a non cross tab query. However the same
expression does not work with a cross tab.

Any thoughts as to why and if there is a workaround?


Crosstab Query
PARAMETERS tst Short;
TRANSFORM Max(PropertyValue.property_value) AS MaxOfproperty_value
SELECT Specification.spec_id, Specification.spec_name
FROM Specification INNER JOIN ((Property INNER JOIN PropertyValue ON
Property.prop_id = PropertyValue.property_id) INNER JOIN (PropertyGroup
INNER
JOIN PropGrpRel ON PropertyGroup.propgroup_id = PropGrpRel.propgroup_id)
ON
Property.prop_id = PropGrpRel.prop_id) ON Specification.spec_id =
PropertyValue.object_id
WHERE (((Specification.spec_type_id)=24) AND
((Specification.spec_owning_id)=[Forms]![Client]![SolutionSelection]))
GROUP BY Specification.spec_id, Specification.spec_name
PIVOT Property.prop_name;


Non Cross tab query
SELECT Specification.*, Specification.spec_owning_id
FROM Specification
WHERE
(((Specification.spec_owning_id)=[Forms]![Client]![SolutionSelection]));
Click to expand...
Click to expand...
 
If it prompts you for [Forms]![Client]![SolutionSelection] then either the form
is not open, or you have mistyped the name of the form or the name of the
control on the form.

The name of the control is not necessarily the same as the name of a field.

Check the control's name and the form's name.
When you say exact name of the parameter are you refering to the form field
like [Forms]![Client]![SolutionSelection] or the name of the field that is
being queryed. At any rate I tried both. If I use the
[Forms]![Client]![SolutionSelection] as the parameter name it simply prompts
me vs. pulling the value from the form. If I use spec_owner_id the name of
the column that has the where clause then I get invalid query.

John Spencer 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
Select the data type of the parameter in column 2


Brady said:
I have a cross tab query with a where clause. I want the where clause to
get
it's value from an open form using the syntax.

[Forms]![Client]![SolutionSelection]

This works just fine with a non cross tab query. However the same
expression does not work with a cross tab.

Any thoughts as to why and if there is a workaround?


Crosstab Query
PARAMETERS tst Short;
TRANSFORM Max(PropertyValue.property_value) AS MaxOfproperty_value
SELECT Specification.spec_id, Specification.spec_name
FROM Specification INNER JOIN ((Property INNER JOIN PropertyValue ON
Property.prop_id = PropertyValue.property_id) INNER JOIN (PropertyGroup
INNER
JOIN PropGrpRel ON PropertyGroup.propgroup_id = PropGrpRel.propgroup_id)
ON
Property.prop_id = PropGrpRel.prop_id) ON Specification.spec_id =
PropertyValue.object_id
WHERE (((Specification.spec_type_id)=24) AND
((Specification.spec_owning_id)=[Forms]![Client]![SolutionSelection]))
GROUP BY Specification.spec_id, Specification.spec_name
PIVOT Property.prop_name;


Non Cross tab query
SELECT Specification.*, Specification.spec_owning_id
FROM Specification
WHERE
(((Specification.spec_owning_id)=[Forms]![Client]![SolutionSelection]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top