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.

 

[steve-rogers]

Allen Browne wrote on 01/16/2009 20:38 ET :

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

Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"croy" wrote in message

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&quot 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?

Thanks
croy

Allen you are a legend. Thank you.