Micorsoft Jet does not recognize forms!form1!Calendar0 as a valid feild name or expression

[sleepguy]

Please help. I am running the following sequence of queries in MS
Access 2000 and receive the error message, "Micorsoft Jet does not
recognize forms!form1!Calendar0 as a valid feild name or expression"

All the Queries up to the cross tab work fine.


Query 1 - SQL
SELECT dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
dbo_tblStudy.PatientGUID, dbo_tblStudy.Creator,
dbo_tblStudy.StudyTypeGUID, dbo_tblStudy.RecordTime,
dbo_tblStudy.FilePath, dbo_tblStudy.Duration, dbo_tblStudy.EegNo,
dbo_tblStudy.Reviewer, dbo_tblStudy.AcquisitionInstrument,
dbo_tblStudy.ToBeReviewedBy, dbo_tblStudy.StudyGroup,
dbo_tblStudy.LastModified, dbo_tblStudy.StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between [Forms]![Form1]![Calendar0]

And [Forms]![Form1]![Calendar1]))
ORDER BY dbo_tblStudy.CreationTime;


Query 2 - SQL
SELECT Qry_Studies_based_on_date_range.StudyGUID,
Qry_Studies_based_on_date_range.Creator,
Qry_Studies_based_on_date_range.RecordTime,
Qry_Studies_based_on_date_range.Duration,
Qry_Studies_based_on_date_range.ToBeReviewedBy,
Qry_Studies_based_on_date_range.StudyName,
Qry_Studies_based_on_date_range.EegNo,
dbo_tblCustomFieldValues.FieldName,
IIf(IsNull(dbo_tblCustomFieldValues!DateValue),dbo_tblCustomFieldValues!Tex­tValue,IIf(dbo_tblCustomFieldValues!DateValue<#1/1/2000#,"X",dbo_tblCustomF­ieldValues!DateValue))

AS [Value], dbo_tblCustomFieldValues.LastModified
FROM Qry_Studies_based_on_date_range LEFT JOIN dbo_tblCustomFieldValues

ON Qry_Studies_based_on_date_range.StudyGUID =
dbo_tblCustomFieldValues.ParentID
WHERE (((dbo_tblCustomFieldValues.FieldName)="SCORER" Or
(dbo_tblCustomFieldValues.FieldName)="DATE SCORED" Or
(dbo_tblCustomFieldValues.FieldName)="DATE READ" Or
(dbo_tblCustomFieldValues.FieldName)="DATE REPORTED"));


Query 3 - SQL - ORIGIN OF THE ERROR MESSAGE ABOVE
TRANSFORM First(Qry_Custom_Add_Custom_Feilds_to_Cases.Value) AS
FirstOfValue
SELECT Qry_Custom_Add_Custom_Feilds_to_Cases.StudyGUID
FROM Qry_Custom_Add_Custom_Feilds_to_Cases
GROUP BY Qry_Custom_Add_Custom_Feilds_to_Cases.StudyGUID
PIVOT Qry_Custom_Add_Custom_Feilds_to_Cases.FieldName;


Thanks in advance for any tips you may lend.

 

[Guest]

A crosstab query must have parameters defined even if the parameter call is
in a feeder query.

Open the query in design view, click on menu Query, Parameters. Enter
[Forms]![Form1]![Calendar0] and [Forms]![Form1]![Calendar1] with datatype
as text. Save the query.

You can then view the SQL to see how it inserted the parameters.

 

[John Vinson]

Please help. I am running the following sequence of queries in MS
Access 2000 and receive the error message, "Micorsoft Jet does not
recognize forms!form1!Calendar0 as a valid feild name or expression"

All the Queries up to the cross tab work fine.

Any query ALLOWS you to use the Parameters collection. Crosstab
queries REQUIRE you to do so!

Try

PARAMETERS [Forms]![form1]![Calendar0] AS <whatever the datatype is>;
TRANSFORM First(Qry_Custom_Add_Custom_Feilds_to_Cases.Value) AS
FirstOfValue
SELECT Qry_Custom_Add_Custom_Feilds_to_Cases.StudyGUID
FROM Qry_Custom_Add_Custom_Feilds_to_Cases
GROUP BY Qry_Custom_Add_Custom_Feilds_to_Cases.StudyGUID
PIVOT Qry_Custom_Add_Custom_Feilds_to_Cases.FieldName;

or rightclick the table background in the design window and select
Parameters.

John W. Vinson[MVP]