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

  • Thread starter Thread starter sleepguy
  • Start date Start date
S

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!TextValue,IIf(dbo_tblCustomFieldValues!DateValue<#1/1/2000#,"X",dbo_tblCustomFieldValues!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.
 
Hi,


You are accessing the data through the User Interface? or through the code?
The syntax FORMS!FormName!ControlName is only acceptable from the User
Interface, from DXXX( ) domain functions, for properties of forms and
controls, and from DoCmd object (maybe at a couple of other places too), but
NOT from CurrentDb object, so

Set rst = CurrentDb.OpenRecordset( strSQL )

won't work as expected. If you access a saved query, you may try something
like:


Dim qdf As QueryDef: Set qdf = CurrentDb.QueryDefs("myQuery")
Dim param As DAO.Parameter
For each param in qdf.Parameters
param.Value=eval(param.Name) ' <---
Next

Set rst=qdf.OpenRecordset( ... )



That means that the parameter name is an evaluable syntax, for sure, which
FORMS!formName!ControlName is!




Hoping it may help,
Vanderghast, Access MVP


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!TextValue,IIf(dbo_tblCustomFieldValues!DateValue<#1/1/2000#,"X",dbo_tblCustomFieldValues!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.
Click to expand...
 
Back
Top