The Microsoft Jet database engine does not recognize...

[croy]

Two querys, the first one feeding the second.

The first query runs fine, but the second throws an error,
complaining that a data source in the first query isn't
acceptable.

This query (qry0041_cht2) works fine:

SELECT tblIvSurv.SurveyDate, tblHours.ChartHour,
[NumberAnglers]*[AnglerHours] AS AnglersPerHour,
IIf([ChartHour] Between [IvTime] And
([IvTime]-([HrsFished]/24)),1,0) AS AnglerHours,
IIf((Weekday([SurveyDate])=1) Or
(Weekday([SurveyDate])=7),"Weekend Day","Weekday") AS
DayType, tblIvSurv.GeoLocId, tblGeoLoc.GeoLocName
FROM tblHours, (tblGeoLoc INNER JOIN (tblLocPicker INNER
JOIN tblIvSurv ON tblLocPicker.GeoLocId =
tblIvSurv.GeoLocId) ON tblGeoLoc.GeoLocId =
tblIvSurv.GeoLocId) INNER JOIN tblIvDetail ON
tblIvSurv.IvSurvId = tblIvDetail.IvSurvId
WHERE (((tblIvSurv.SurveyDate) Between
[Forms]![frmStartReport_AnglerEffort]![txtFrom] And
[Forms]![frmStartReport_AnglerEffort]![txtTo]) AND
((tblIvDetail.Done)=-1));

But the next query in the chain (qry0041_cht2):

TRANSFORM Sum(qry0041_cht.AnglersPerHour) AS
SumOfAnglersPerHour
SELECT qry0041_cht.ChartHour
FROM qry0041_cht
GROUP BY qry0041_cht.ChartHour
PIVOT qry0041_cht.DayType;


....this error is returned:

"The Microsoft Jet database engine does not recognize
'[Forms]![frmStartReport_AnglerEffort]![txtFrom]' as a valid
field name or expression."

All that feeds the second query is the first one, and the
first one runs fine. How can the second have a problem with
a data source of the first?

 

[Allen Browne]

In the first query, try explicitly declaring your parameters:

1. Open qry0041_cht2 in design view.

2. Choose Parameters on the Query menu.
Access opens a dialog.

3. Enter 2 rows in the dialog:
[Forms]![frmStartReport_AnglerEffort]![txtFrom] Date
[Forms]![frmStartReport_AnglerEffort]![txtTo]) Date

 

[croy]

In the first query, try explicitly declaring your parameters:

1. Open qry0041_cht2 in design view.

2. Choose Parameters on the Query menu.
Access opens a dialog.

3. Enter 2 rows in the dialog:
[Forms]![frmStartReport_AnglerEffort]![txtFrom] Date
[Forms]![frmStartReport_AnglerEffort]![txtTo]) Date

Thanks Allen. I don't think I've ever seen this before.
Where can I learn more?

 

[Allen Browne]

Thanks Allen. I don't think I've ever seen this before.
Where can I learn more?

Here's an article specifically addressing crosstab techniques:
http://allenbrowne.com/ser-67.html

Here's an article with info on parameters, including the one case where you
should not declare them:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

 

[croy]

Here's an article specifically addressing crosstab techniques:
http://allenbrowne.com/ser-67.html

Here's an article with info on parameters, including the one case where you
should not declare them:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

Wow! Thanks. Life-extension would have to make
unimaginable strides before I would have sorted that out on
my own.

 

[David W. Fenton]

Here's an article with info on parameters, including the one case
where you should not declare them:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

That's problematic, though, as Access 2003 (at least) has
difficulties properly evaluating Null controls on forms if you don't
declare a parameter.

My recommendation is to simply avoid situations where you are
hard-coding form control references into saved queries.