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

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.
 
M

Michel Walsh

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

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

Top