Crosstab error: Engine does not recognize <name> - no parameters

[sberti]

Hi,
I have a crosstab query that is giving me the error message:
"The Microsoft Access database engine does not recognize 'h1.H1_Raft' as a valid field name or expression."
The underlying query works fine.
I don't understand this error, since I don't have a table or query called h1 and the field H1_Raft is not in the underlying queries.

I have read that this error can happen if you need parameters defined, but neither this crosstab query or the query below it require parameters.

Here is the underlying query:

SELECT
qryLocation_Present.Farm,
qryLocation_Present.Site,
qryLocation_Present.Row,
qryLocation_Present.Position,
qryLocation_Present.L_Position AS PresentLoc,
qryLocation_Present.Raft,
qryLocation_Present.MaxOfL_Date,
qryRaftList_hatchery_ALL.CurGrams,
IIf([L_Position]>0,IIf([Broodstock]=Yes,"BRD",IIf([SRED]=Yes,"SRED","PR")),"") AS Type

FROM
qryLocation_Present

LEFT JOIN
qryRaftList_hatchery_ALL

ON
qryLocation_Present.Raft = qryRaftList_hatchery_ALL.Raft;

Here is my crosstab query:

TRANSFORM
Avg(qryLoc_ALL.Raft) AS AvgOfRaft
SELECT
qryLoc_ALL.Site,
qryLoc_ALL.Position
FROM
qryLoc_ALL
GROUP BY
qryLoc_ALL.Site, qryLoc_ALL.Position
ORDER BY
qryLoc_ALL.Site, qryLoc_ALL.Position, qryLoc_ALL.Row
PIVOT qryLoc_ALL.Row;

Can anyone point me in the direction I should go to fix this?

[CJ_London]

I presume your first query is called qryLoc_ALL. If this is the case it references qryLocation_Present and qryRaftList_hatchery_ALL - are there parameters in either of these two queries?

Also Type is a reserved word which may result in a misleading error message - recommend changing it to a more meaningful description. At the very least, put square brackets round it which usually works. Or remove it all together since you ar enot using it in the crosstab

[sberti]

Thanks for your reply.

There are no parameters in the underlying queries.

I have changed the field name from "Type" to "RaftType". (I could not remove it since I have a 2nd query running off the query qryLoc_ALL that needs it.)

I'm still getting this same error in the crosstab.

I have tried making other crosstab queries using the query qryLoc_ALL as the basis and continue to get the same error. I even tried making one that does not include the field qryLocation_Present.Raft, which is the only field that contains the name "Raft" in it.

This crosstab query used to work. I've been looking through the data for data errors, but don't see any. But I'm not quite sure what kinds of things in the data of a crosstab query can cause an error.

[Micron]

Maybe the called query or a table it is based on is not native to the same database? The h1 could be a pseudonym for a db that cannot be found.

[CJ_London]

are you sure you have not set any parameters - even if you are not using them?

the other thing to do is to check (all) the query properties for sorting and filtering

[Micron]

The problem may be here

IIf([L_Position]>0,IIf([Broodstock]=Yes,"BRD",IIf([SRED]=Yes,"SRED","PR")),"")

I suspect [Broodstock] and [SRED] are form controls. In that case, I believe a crosstab doesn't work if there's an underlying parameter that isn't explicitly declared. Try going to the underlying query(ies) and explicitly defining all such references in the query property sheet.