Query Parameter Failure

G

Guest

I have a query with a parameter from a form that runs just fine, but if I try
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.

Any idea what the problem/solution might be?

-Karen


The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));

The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;
 
G

Guest

Karen,

two options:
base your crosstab on a query that has already been paramatized so the
crosstab doesn't actually need a query or place your parameters directly in
the parameter dialog which you can access by choosing querytype - parameters.

hth
 
G

Guest

Hi Maurice,
I think I'm already doing your first suggestion. The query with the
parameter is just a Select query (the first one in my other post); the cross
tab is based on that Select query and has no criteria or parameter in it.
The Select query runs fine by itself; the cross tab gives me an error on the
parameter in the Select query.

I've messed around with the parameters in the dialog box to no avail. While
it does work with 'local' query parameters, I'd rather not make the user
re-enter the information for this one query since it's part of a matching set
of data exports. And it bothers me that this One is nearly identical to one
of the other exports that works fine with the form based parameters (I use
that one for copy-paste to reduce syntax errors for the parameters).

Any other ideas?

-K


Maurice said:
Karen,

two options:
base your crosstab on a query that has already been paramatized so the
crosstab doesn't actually need a query or place your parameters directly in
the parameter dialog which you can access by choosing querytype - parameters.

hth
--
Maurice Ausum


Karen said:
I have a query with a parameter from a form that runs just fine, but if I try
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.

Any idea what the problem/solution might be?

-Karen


The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));

The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;
Click to expand...
Click to expand...
 
G

Guest

Hi Karen,

It is indeed a strange situation. Why don't you have a look at:

http://support.microsoft.com/Default.aspx?id=209778

to see if this might shed some light on it.

hth
--
Maurice Ausum


Karen said:
Hi Maurice,
I think I'm already doing your first suggestion. The query with the
parameter is just a Select query (the first one in my other post); the cross
tab is based on that Select query and has no criteria or parameter in it.
The Select query runs fine by itself; the cross tab gives me an error on the
parameter in the Select query.

I've messed around with the parameters in the dialog box to no avail. While
it does work with 'local' query parameters, I'd rather not make the user
re-enter the information for this one query since it's part of a matching set
of data exports. And it bothers me that this One is nearly identical to one
of the other exports that works fine with the form based parameters (I use
that one for copy-paste to reduce syntax errors for the parameters).

Any other ideas?

-K


Maurice said:
Karen,

two options:
base your crosstab on a query that has already been paramatized so the
crosstab doesn't actually need a query or place your parameters directly in
the parameter dialog which you can access by choosing querytype - parameters.

hth
--
Maurice Ausum


Karen said:
I have a query with a parameter from a form that runs just fine, but if I try
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.

Any idea what the problem/solution might be?

-Karen


The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));

The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That did help (it works now). I'll swear I'd tried that, but maybe
something wasn't all the way right with it. Thanks!

It's still a little befuddling though. The parameter from the form was in a
select query a nest or two before the crosstab and in a field that was not
used for column definitions, which is what seemed to be the problem described
by your link. But columns still had something to do with it. I got the
similar yet functioning crosstab to give me the same error simply by removing
the explicit column names in the query properties (there were just a couple
fixed ones so I'd typed them in, unlike the broken crosstab).


Maurice said:
Hi Karen,

It is indeed a strange situation. Why don't you have a look at:

http://support.microsoft.com/Default.aspx?id=209778

to see if this might shed some light on it.

hth
--
Maurice Ausum


Karen said:
Hi Maurice,
I think I'm already doing your first suggestion. The query with the
parameter is just a Select query (the first one in my other post); the cross
tab is based on that Select query and has no criteria or parameter in it.
The Select query runs fine by itself; the cross tab gives me an error on the
parameter in the Select query.

I've messed around with the parameters in the dialog box to no avail. While
it does work with 'local' query parameters, I'd rather not make the user
re-enter the information for this one query since it's part of a matching set
of data exports. And it bothers me that this One is nearly identical to one
of the other exports that works fine with the form based parameters (I use
that one for copy-paste to reduce syntax errors for the parameters).

Any other ideas?

-K


Maurice said:
Karen,

two options:
base your crosstab on a query that has already been paramatized so the
crosstab doesn't actually need a query or place your parameters directly in
the parameter dialog which you can access by choosing querytype - parameters.

hth
--
Maurice Ausum


:

I have a query with a parameter from a form that runs just fine, but if I try
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.

Any idea what the problem/solution might be?

-Karen


The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));

The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Karen,

Glad to hear it helped you out. I understand the confusion I'm stumped with
it from time to time asking myself why did it work with the first one and not
with the second one. But he that's what makes it interesting ;-)

--
Maurice Ausum


Karen said:
That did help (it works now). I'll swear I'd tried that, but maybe
something wasn't all the way right with it. Thanks!

It's still a little befuddling though. The parameter from the form was in a
select query a nest or two before the crosstab and in a field that was not
used for column definitions, which is what seemed to be the problem described
by your link. But columns still had something to do with it. I got the
similar yet functioning crosstab to give me the same error simply by removing
the explicit column names in the query properties (there were just a couple
fixed ones so I'd typed them in, unlike the broken crosstab).


Maurice said:
Hi Karen,

It is indeed a strange situation. Why don't you have a look at:

http://support.microsoft.com/Default.aspx?id=209778

to see if this might shed some light on it.

hth
--
Maurice Ausum


Karen said:
Hi Maurice,
I think I'm already doing your first suggestion. The query with the
parameter is just a Select query (the first one in my other post); the cross
tab is based on that Select query and has no criteria or parameter in it.
The Select query runs fine by itself; the cross tab gives me an error on the
parameter in the Select query.

I've messed around with the parameters in the dialog box to no avail. While
it does work with 'local' query parameters, I'd rather not make the user
re-enter the information for this one query since it's part of a matching set
of data exports. And it bothers me that this One is nearly identical to one
of the other exports that works fine with the form based parameters (I use
that one for copy-paste to reduce syntax errors for the parameters).

Any other ideas?

-K


:

Karen,

two options:
base your crosstab on a query that has already been paramatized so the
crosstab doesn't actually need a query or place your parameters directly in
the parameter dialog which you can access by choosing querytype - parameters.

hth
--
Maurice Ausum


:

I have a query with a parameter from a form that runs just fine, but if I try
to base a cross-tab query it, I get an error that the Jet engine doesn't
recognize the parameter in the first query as being a valid field name or
exression. I can do a basic select query based on it without the error. If
I give a plain value, the cross-tab will run with no errors. The parameter
syntax should be fine since I've copied it from a nearly identical query
based on the same form.

Any idea what the problem/solution might be?

-Karen


The query with parameter as SQL is:
SELECT [Projects Billings Summarized by LCat query].PName, [Projects
Billings Summarized by LCat query].ProjectID, [Projects Billings Summarized
by LCat query].ActivePlan, [Projects Billings Summarized by LCat
query].Closed, [Projects Billings Summarized by LCat query].RDate, [Projects
Billings Summarized by LCat query].LCategory,
IIf(IsNull([aspendsum])=True,[fspendsum],[aspendsum]) AS LCatSpend,
IsNull([aspendsum]) AS NoAct
FROM [Projects Billings Summarized by LCat query] INNER JOIN Projects ON
([Projects Billings Summarized by LCat query].ActivePlan =
Projects.Activated) AND ([Projects Billings Summarized by LCat
query].ProjectID = Projects.ProjectID)
WHERE ((([Projects Billings Summarized by LCat
query].ProjectID)=[Forms]![QUERY TESTING]![ProjectIDPick]) AND (([Projects
Billings Summarized by LCat query].LCategory)<>"N/A"));

The simplest cross-tab as SQL (gives no error if a value is substituted for
the ProjectIDPick parameter):
TRANSFORM Sum([Program Review Export 4a Labor Cats].LCatSpend) AS
SumOfLCatSpend
SELECT [Program Review Export 4a Labor Cats].RDate
FROM [Program Review Export 4a Labor Cats]
GROUP BY [Program Review Export 4a Labor Cats].RDate
PIVOT [Program Review Export 4a Labor Cats].LCategory;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query Input Failure 2

Top