"does not recognize as valid field", but prompts when parameters d

[Guest]

I have a crosstab query that calls on a query that calls on other queries...
you get the idea.

I'm getting the "the Microsoft Jet database engine does not recognize...."
- you know the rest.

Looking on these threads, everyone says "you must declare your paramenters"
when a crosstab call on a query. Fair enough.

But now, after using both the menus and straight SQL for "PARAMETERS"
declarations, I get prompts to manually enter values.

Clearly something is wrong...

First query:

SELECT [tblDistricts].[DistrictID], [tblSWLs]![FName] & " " &
[tblSWLs]![LName] AS SWL, 1+[tblSWLs]![PrimaryContact] AS xPrimme
FROM tblSWLs RIGHT JOIN (tblDistricts LEFT JOIN tblDistrictsandSWLs ON
[tblDistricts].[DistrictID]=[tblDistrictsandSWLs].[DistrictID]) ON
[tblSWLs].[PLID]=[tblDistrictsandSWLs].[PLID]
ORDER BY [tblDistricts].[DistrictID], 1+[tblSWLs]![PrimaryContact],
[tblSWLs]![FName] & " " & [tblSWLs]![LName];

Second Query:

SELECT [xqrySWL000].[DistrictID], [xqrySWL000].[SWL], (SELECT Count(*) FROM
xqrySWL000 As X WHERE (X.DistrictID=xqrySWL000.DistrictID) AND (( X.xPrimme

xqrySWL000.xPrimme) OR (( X.xPrimme = xqrySWL000.xPrimme) AND

(xqrySWL000.SWL<=X.SWL)))+1) AS Num
FROM xqrySWL000;

Crosstab query where I get the error -

TRANSFORM First(xqrySWL001.SWL) AS FirstOfSWL
SELECT xqrySWL001.DistrictID
FROM xqrySWL001
GROUP BY xqrySWL001.DistrictID
PIVOT xqrySWL001.Num;

Thanks in advance for any advice you may have.

 

[Duane Hookom]

It looks like you are attempting to use a subquery from the second query as
a column heading in the crosstab. Crosstabs generally don't like that. I
don't know if there is anything you can do other than replace the subquery
with DCount().
--
Duane Hookom
MS Access MVP

I have a crosstab query that calls on a query that calls on other
queries...
you get the idea.

I'm getting the "the Microsoft Jet database engine does not recognize...."
- you know the rest.

Looking on these threads, everyone says "you must declare your
paramenters"
when a crosstab call on a query. Fair enough.

But now, after using both the menus and straight SQL for "PARAMETERS"
declarations, I get prompts to manually enter values.

Clearly something is wrong...

First query:

SELECT [tblDistricts].[DistrictID], [tblSWLs]![FName] & " " &
[tblSWLs]![LName] AS SWL, 1+[tblSWLs]![PrimaryContact] AS xPrimme
FROM tblSWLs RIGHT JOIN (tblDistricts LEFT JOIN tblDistrictsandSWLs ON
[tblDistricts].[DistrictID]=[tblDistrictsandSWLs].[DistrictID]) ON
[tblSWLs].[PLID]=[tblDistrictsandSWLs].[PLID]
ORDER BY [tblDistricts].[DistrictID], 1+[tblSWLs]![PrimaryContact],
[tblSWLs]![FName] & " " & [tblSWLs]![LName];

Second Query:

SELECT [xqrySWL000].[DistrictID], [xqrySWL000].[SWL], (SELECT Count(*)
FROM
xqrySWL000 As X WHERE (X.DistrictID=xqrySWL000.DistrictID) AND ((
X.xPrimme

xqrySWL000.xPrimme) OR (( X.xPrimme = xqrySWL000.xPrimme) AND

(xqrySWL000.SWL<=X.SWL)))+1) AS Num
FROM xqrySWL000;

Crosstab query where I get the error -

TRANSFORM First(xqrySWL001.SWL) AS FirstOfSWL
SELECT xqrySWL001.DistrictID
FROM xqrySWL001
GROUP BY xqrySWL001.DistrictID
PIVOT xqrySWL001.Num;

Thanks in advance for any advice you may have.

 

[Guest]

Crosstab displays an error when you add criteria unless you define the
parameters.

Open the query in design view and click on menu VIEW - View SQL. The first
line should now start with TRANSFORM. You need to add like this before that
line --
PARAMETERS [enter beginning number] Text ( 255 ), [enter ending
number] Text ( 255 );

This example has two parameters but you could have one or more separating by
a comma and ending with a semicolon.

I have a crosstab query that calls on a query that calls on other queries...
you get the idea.

I'm getting the "the Microsoft Jet database engine does not recognize...."
- you know the rest.

Looking on these threads, everyone says "you must declare your paramenters"
when a crosstab call on a query. Fair enough.

But now, after using both the menus and straight SQL for "PARAMETERS"
declarations, I get prompts to manually enter values.

Clearly something is wrong...

First query:

SELECT [tblDistricts].[DistrictID], [tblSWLs]![FName] & " " &
[tblSWLs]![LName] AS SWL, 1+[tblSWLs]![PrimaryContact] AS xPrimme
FROM tblSWLs RIGHT JOIN (tblDistricts LEFT JOIN tblDistrictsandSWLs ON
[tblDistricts].[DistrictID]=[tblDistrictsandSWLs].[DistrictID]) ON
[tblSWLs].[PLID]=[tblDistrictsandSWLs].[PLID]
ORDER BY [tblDistricts].[DistrictID], 1+[tblSWLs]![PrimaryContact],
[tblSWLs]![FName] & " " & [tblSWLs]![LName];

Second Query:

SELECT [xqrySWL000].[DistrictID], [xqrySWL000].[SWL], (SELECT Count(*) FROM
xqrySWL000 As X WHERE (X.DistrictID=xqrySWL000.DistrictID) AND (( X.xPrimme

xqrySWL000.xPrimme) OR (( X.xPrimme = xqrySWL000.xPrimme) AND

(xqrySWL000.SWL<=X.SWL)))+1) AS Num
FROM xqrySWL000;

Crosstab query where I get the error -

TRANSFORM First(xqrySWL001.SWL) AS FirstOfSWL
SELECT xqrySWL001.DistrictID
FROM xqrySWL001
GROUP BY xqrySWL001.DistrictID
PIVOT xqrySWL001.Num;

Thanks in advance for any advice you may have.

 

[Guest]

Karl,

Yes, I know that, and I think I mentioned specifically that I know that,
and, as I mentioned also in my note, I did declare the parameters a number of
different ways, so instead of showing each and every different method used,
all of which led to the same problem, I put the basic SQL SELECT language so
people could advise on the underlying problem.

My problem isn't the "does not recognize" message, it's the fact that it
prompts for input when I do declare the parameters.

This is one case where someone actually tried reading the multiple other
times the similar question was raised before posting...

I appreciate the attempt, thought.

Crosstab displays an error when you add criteria unless you define the
parameters.

Open the query in design view and click on menu VIEW - View SQL. The first
line should now start with TRANSFORM. You need to add like this before that
line --
PARAMETERS [enter beginning number] Text ( 255 ), [enter ending
number] Text ( 255 );

This example has two parameters but you could have one or more separating by
a comma and ending with a semicolon.

I have a crosstab query that calls on a query that calls on other queries...
you get the idea.

I'm getting the "the Microsoft Jet database engine does not recognize...."
- you know the rest.

Looking on these threads, everyone says "you must declare your paramenters"
when a crosstab call on a query. Fair enough.

But now, after using both the menus and straight SQL for "PARAMETERS"
declarations, I get prompts to manually enter values.

Clearly something is wrong...

First query:

SELECT [tblDistricts].[DistrictID], [tblSWLs]![FName] & " " &
[tblSWLs]![LName] AS SWL, 1+[tblSWLs]![PrimaryContact] AS xPrimme
FROM tblSWLs RIGHT JOIN (tblDistricts LEFT JOIN tblDistrictsandSWLs ON
[tblDistricts].[DistrictID]=[tblDistrictsandSWLs].[DistrictID]) ON
[tblSWLs].[PLID]=[tblDistrictsandSWLs].[PLID]
ORDER BY [tblDistricts].[DistrictID], 1+[tblSWLs]![PrimaryContact],
[tblSWLs]![FName] & " " & [tblSWLs]![LName];

Second Query:

SELECT [xqrySWL000].[DistrictID], [xqrySWL000].[SWL], (SELECT Count(*) FROM
xqrySWL000 As X WHERE (X.DistrictID=xqrySWL000.DistrictID) AND (( X.xPrimme

xqrySWL000.xPrimme) OR (( X.xPrimme = xqrySWL000.xPrimme) AND

(xqrySWL000.SWL<=X.SWL)))+1) AS Num
FROM xqrySWL000;

Crosstab query where I get the error -

TRANSFORM First(xqrySWL001.SWL) AS FirstOfSWL
SELECT xqrySWL001.DistrictID
FROM xqrySWL001
GROUP BY xqrySWL001.DistrictID
PIVOT xqrySWL001.Num;

Thanks in advance for any advice you may have.

 

[Guest]

Can I rank or number the records in a subset using DCount??

We're grouping by District ID. The District can have a single contact/rep
or more, it varies. So... I had my queries rank them. There is a "Yes/No"
flag for a primary contact, which gets the #1, rank, then the rest get
numbered in alpha order.

If I output it using a make table query, the crosstab has no problems
pulling the ordinal value (rank 1, 2, 3 or whatever) as a column head. The
reason why I'm trying to reference subqueries is because the reps are a very
dynamic group, with districts swapping them back and forth, dropping and
adding, so the max and minimum numbers as well as the actual data are not
static.

It looks like you are attempting to use a subquery from the second query as
a column heading in the crosstab. Crosstabs generally don't like that. I
don't know if there is anything you can do other than replace the subquery
with DCount().
--
Duane Hookom
MS Access MVP

I have a crosstab query that calls on a query that calls on other
queries...
you get the idea.

I'm getting the "the Microsoft Jet database engine does not recognize...."
- you know the rest.

Looking on these threads, everyone says "you must declare your
paramenters"
when a crosstab call on a query. Fair enough.

But now, after using both the menus and straight SQL for "PARAMETERS"
declarations, I get prompts to manually enter values.

Clearly something is wrong...

First query:

SELECT [tblDistricts].[DistrictID], [tblSWLs]![FName] & " " &
[tblSWLs]![LName] AS SWL, 1+[tblSWLs]![PrimaryContact] AS xPrimme
FROM tblSWLs RIGHT JOIN (tblDistricts LEFT JOIN tblDistrictsandSWLs ON
[tblDistricts].[DistrictID]=[tblDistrictsandSWLs].[DistrictID]) ON
[tblSWLs].[PLID]=[tblDistrictsandSWLs].[PLID]
ORDER BY [tblDistricts].[DistrictID], 1+[tblSWLs]![PrimaryContact],
[tblSWLs]![FName] & " " & [tblSWLs]![LName];

Second Query:

SELECT [xqrySWL000].[DistrictID], [xqrySWL000].[SWL], (SELECT Count(*)
FROM
xqrySWL000 As X WHERE (X.DistrictID=xqrySWL000.DistrictID) AND ((
X.xPrimme

xqrySWL000.xPrimme) OR (( X.xPrimme = xqrySWL000.xPrimme) AND

(xqrySWL000.SWL<=X.SWL)))+1) AS Num
FROM xqrySWL000;

Crosstab query where I get the error -

TRANSFORM First(xqrySWL001.SWL) AS FirstOfSWL
SELECT xqrySWL001.DistrictID
FROM xqrySWL001
GROUP BY xqrySWL001.DistrictID
PIVOT xqrySWL001.Num;

Thanks in advance for any advice you may have.

 

[Duane Hookom]

You can slowly rank your records using DCount(). I didn't want to figure out
your expression and didn't have a clue about datatypes. A place to start
might be replacing your subquery with something like (errrors expected)
DCount("*","xqrySWL000", "(DistrictID=" & DistrictID AND xPrimme > " &
xPrimme" & ") OR (xPrimme = " & xPrimme & " AND
SWL<=" & SWL & ")+1)

--
Duane Hookom
MS Access MVP

Can I rank or number the records in a subset using DCount??

We're grouping by District ID. The District can have a single contact/rep
or more, it varies. So... I had my queries rank them. There is a
"Yes/No"
flag for a primary contact, which gets the #1, rank, then the rest get
numbered in alpha order.

If I output it using a make table query, the crosstab has no problems
pulling the ordinal value (rank 1, 2, 3 or whatever) as a column head.
The
reason why I'm trying to reference subqueries is because the reps are a
very
dynamic group, with districts swapping them back and forth, dropping and
adding, so the max and minimum numbers as well as the actual data are not
static.

It looks like you are attempting to use a subquery from the second query
as
a column heading in the crosstab. Crosstabs generally don't like that. I
don't know if there is anything you can do other than replace the
subquery
with DCount().
--
Duane Hookom
MS Access MVP

I have a crosstab query that calls on a query that calls on other
queries...
you get the idea.

I'm getting the "the Microsoft Jet database engine does not
recognize...."
- you know the rest.

Looking on these threads, everyone says "you must declare your
paramenters"
when a crosstab call on a query. Fair enough.

But now, after using both the menus and straight SQL for "PARAMETERS"
declarations, I get prompts to manually enter values.

Clearly something is wrong...

First query:

SELECT [tblDistricts].[DistrictID], [tblSWLs]![FName] & " " &
[tblSWLs]![LName] AS SWL, 1+[tblSWLs]![PrimaryContact] AS xPrimme
FROM tblSWLs RIGHT JOIN (tblDistricts LEFT JOIN tblDistrictsandSWLs ON
[tblDistricts].[DistrictID]=[tblDistrictsandSWLs].[DistrictID]) ON
[tblSWLs].[PLID]=[tblDistrictsandSWLs].[PLID]
ORDER BY [tblDistricts].[DistrictID], 1+[tblSWLs]![PrimaryContact],
[tblSWLs]![FName] & " " & [tblSWLs]![LName];

Second Query:

SELECT [xqrySWL000].[DistrictID], [xqrySWL000].[SWL], (SELECT Count(*)
FROM
xqrySWL000 As X WHERE (X.DistrictID=xqrySWL000.DistrictID) AND ((
X.xPrimme
xqrySWL000.xPrimme) OR (( X.xPrimme = xqrySWL000.xPrimme) AND
(xqrySWL000.SWL<=X.SWL)))+1) AS Num
FROM xqrySWL000;

Crosstab query where I get the error -

TRANSFORM First(xqrySWL001.SWL) AS FirstOfSWL
SELECT xqrySWL001.DistrictID
FROM xqrySWL001
GROUP BY xqrySWL001.DistrictID
PIVOT xqrySWL001.Num;

Thanks in advance for any advice you may have.