Crosstab query with parameters tht are a subquery

[Guest]

I am getting an error message when trying to run a crosstab query (Q1) based
on a query with parameters (Q2). The base query's (Q2) parameter is a
subquery. I've read that I need to define my parameters before the crosstab
will work but when I put my subquery in as my parameter I get an error
message that I have the incorrect syntax. Please help.

Here is my base query (Q2):

SELECT tblBrnHeader.Brn_ID
FROM tblBrnHeader
WHERE (((tblBrnHeader.Brn_ID) In (SELECT tblEmpSecurityBranch.Brn_ID FROM
tblEmpSecurityBranch WHERE
(((tblEmpSecurityBranch.UserName)=[currentuser])))));

 

[Michel Walsh]

Hi,


In a XTab, you have to declare the parameter used anywhere (not the subquery
themselves, but the parameter they used) in the XTab. It is in the toolbar,
when the query is in the designer, under Query | Parameters...

Supply the parameters names, and their data type.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

The problem is the parameter is a subquery so what do I call it in the query
parameters? Here is the subquery again:

In (SELECT tblEmpSecurityBranch.Brn_ID
FROM tblEmpSecurityBranch
WHERE (((tblEmpSecurityBranch.UserName)=[currentuser])))));

Hi,


In a XTab, you have to declare the parameter used anywhere (not the subquery
themselves, but the parameter they used) in the XTab. It is in the toolbar,
when the query is in the designer, under Query | Parameters...

Supply the parameters names, and their data type.


Hoping it may help,
Vanderghast, Access MVP

I am getting an error message when trying to run a crosstab query (Q1)
based
on a query with parameters (Q2). The base query's (Q2) parameter is a
subquery. I've read that I need to define my parameters before the
crosstab
will work but when I put my subquery in as my parameter I get an error
message that I have the incorrect syntax. Please help.

Here is my base query (Q2):

SELECT tblBrnHeader.Brn_ID
FROM tblBrnHeader
WHERE (((tblBrnHeader.Brn_ID) In (SELECT tblEmpSecurityBranch.Brn_ID FROM
tblEmpSecurityBranch WHERE
(((tblEmpSecurityBranch.UserName)=[currentuser])))));

 

[Guest]

Seems to me that [currentuser] is the parameter that needs to be declared.

The problem is the parameter is a subquery so what do I call it in the query
parameters? Here is the subquery again:

In (SELECT tblEmpSecurityBranch.Brn_ID
FROM tblEmpSecurityBranch
WHERE (((tblEmpSecurityBranch.UserName)=[currentuser])))));

Hi,


In a XTab, you have to declare the parameter used anywhere (not the subquery
themselves, but the parameter they used) in the XTab. It is in the toolbar,
when the query is in the designer, under Query | Parameters...

Supply the parameters names, and their data type.


Hoping it may help,
Vanderghast, Access MVP

I am getting an error message when trying to run a crosstab query (Q1)
based
on a query with parameters (Q2). The base query's (Q2) parameter is a
subquery. I've read that I need to define my parameters before the
crosstab
will work but when I put my subquery in as my parameter I get an error
message that I have the incorrect syntax. Please help.

Here is my base query (Q2):

SELECT tblBrnHeader.Brn_ID
FROM tblBrnHeader
WHERE (((tblBrnHeader.Brn_ID) In (SELECT tblEmpSecurityBranch.Brn_ID FROM
tblEmpSecurityBranch WHERE
(((tblEmpSecurityBranch.UserName)=[currentuser])))));

 

[Michel Walsh]

Hi,


that is also what it seems to me. The place where is the parameter, directly
in the "main" where clause, or embedded deeply in a sub-query, or even is a
saved query (used as table) itself using it... it is still ... a
parameter: a name that can be used where a field name can be used, but which
is not defined in any involved tables.


Vanderghast, Access MVP

Seems to me that [currentuser] is the parameter that needs to be
declared.

The problem is the parameter is a subquery so what do I call it in the
query
parameters? Here is the subquery again:

In (SELECT tblEmpSecurityBranch.Brn_ID
FROM tblEmpSecurityBranch
WHERE (((tblEmpSecurityBranch.UserName)=[currentuser])))));

Hi,


In a XTab, you have to declare the parameter used anywhere (not the
subquery
themselves, but the parameter they used) in the XTab. It is in the
toolbar,
when the query is in the designer, under Query | Parameters...

Supply the parameters names, and their data type.


Hoping it may help,
Vanderghast, Access MVP


I am getting an error message when trying to run a crosstab query (Q1)
based
on a query with parameters (Q2). The base query's (Q2) parameter is
a
subquery. I've read that I need to define my parameters before the
crosstab
will work but when I put my subquery in as my parameter I get an
error
message that I have the incorrect syntax. Please help.

Here is my base query (Q2):

SELECT tblBrnHeader.Brn_ID
FROM tblBrnHeader
WHERE (((tblBrnHeader.Brn_ID) In (SELECT tblEmpSecurityBranch.Brn_ID
FROM
tblEmpSecurityBranch WHERE
(((tblEmpSecurityBranch.UserName)=[currentuser])))));

 

[Duane Hookom]

Crosstabs generally puke when they confront a subquery. If Currentuser is a
field in tblBrnHeader, you may need to create a join with the tables.

 

[Guest]

Thank you all for you help. It was the [currentuser] parameter that needed
to be declared, not the entire subquery.

Crosstabs generally puke when they confront a subquery. If Currentuser is a
field in tblBrnHeader, you may need to create a join with the tables.

--
Duane Hookom
MS Access MVP

I am getting an error message when trying to run a crosstab query (Q1)
based
on a query with parameters (Q2). The base query's (Q2) parameter is a
subquery. I've read that I need to define my parameters before the
crosstab
will work but when I put my subquery in as my parameter I get an error
message that I have the incorrect syntax. Please help.

Here is my base query (Q2):

SELECT tblBrnHeader.Brn_ID
FROM tblBrnHeader
WHERE (((tblBrnHeader.Brn_ID) In (SELECT tblEmpSecurityBranch.Brn_ID FROM
tblEmpSecurityBranch WHERE
(((tblEmpSecurityBranch.UserName)=[currentuser])))));