Software

General discussion

Gravatar
0 Votes
Locked

report works, query doesn't

By john_wills ·
In Access 2000 I have a report based on a query. The report works as desired. When I start the query by itself I get "Invalid operation" and an empty Help screen. I also get "Invalid operation" when I try to start a new form I am basing on the same query. I have executed the query by itself in the past, and I do not believe I have made changes in it without executing the query directly before running the report. What might I be doing wrong?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

report works, query doesn't

by nikki96 In reply to report works, query doesn ...

You might be referencing some member of an object, such as the report, a variable, a form element, or anything with "Me!" in it in the query. Since that object isn't defined outside the report, you'd get errors.

Post the query if you still have problems.

gravatar
Collapse -

report works, query doesn't

by john_wills In reply to report works, query doesn ...

SELECT AB3632IEPDueDate, AB3632IEPStart, AB3632IEPStop, Address1, Address2, Address3, AlternativeAddress2, AlternativeCity,AlternativeInfo, AlternativeOthPhone, AlternativePhone, AlternativeState, AlternativeZip, AssessmentReportDueDate, AssessorType, Exp_Date AS AuthorizationEnd, [Authorizations].[Start_Date] AS AuthorizationStart,BIRTHDATE,BIS, CensusTract, City, Client_Event_Date, ClientStatusCode, ConsentReceived, CREAT_DATE, DayTreatmentReferral,DHS_Client, EducationLevel, FIRST_NAME,FMP_Client, Home_Phone, Prov_Name AS InitialAssessmentAuthorization, IIf(IsNull([Insurance_Num])," - ",[Insurance_Num]) AS InsuranceNumber, LAST_NAME, Letter1Sent,Letter2Sent,LivingSituation, MIDDLE, NewSchool, NOTE, Oth_Phone, OtherCountyDepartment,OutOfCountySFDept, ParentLanguage, Probation, ProgReferralSent,Race, Race_Descript,Re_Referral,ReferralConfirmation,ReferralDate, RefSchool,ReferralSentTo, ResidentialRequest, PHYS_ADDR1 AS SchoolAddr1, [dbo_PROVSITE].[PHYS_ADDR2] AS SchoolAddr2, [dbo_PROVSITE].[PHYS_ADDR3] AS SchoolAddr3, [dbo_PROVSITE].[PHYS_CITY] AS SchoolCity, SchoolFax, [dbo_PROVIDER].[NAME] AS SchoolName, [dbo_PROVSITE].[PHYS_PHONE] AS SchoolPhone, [dbo_PROVSITE].[PHYS_STATE] AS SchoolState, [dbo_PROVSITE].[PHYS_ZIP] AS SchoolZip, SEX, SFUSD_ID, SFUSDMemoSent, SpecialEdCode, SpecialEdTeacher, SS_NUMBER, State, Teacher2, Teacher2Phone, TeacherPhone, TransferFromOtherCounty, Work_Phone, Zip_Code

gravatar
Collapse -

report works, query doesn't

by john_wills In reply to report works, query doesn ...

rest of query:

FROM ((((((EventArray LEFT JOIN dbo_PROVIDER ON RefSchool=EXT_ID) LEFT JOIN dbo_PROVSITE ON [dbo_PROVIDER].[PROV_ID]=[dbo_PROVSITE].[PROV_ID]) LEFT JOIN DemographyHigh ON ([EventArray].[Client_Event_Date]=[DemographyHigh].[Client_Event_Date]) AND ([EventArray].[Patient_ID]=[DemographyHigh].[PATIENT_ID])) LEFT JOIN InsuranceHigh ON ([EventArray].[Client_Event_Date]=[InsuranceHigh].[Client_Event_Date]) AND ([EventArray].[Patient_ID]=[InsuranceHigh].[Patient_ID])) LEFT JOIN dbo_EVENTLOG ON ([EventArray].[Patient_ID]=[dbo_EVENTLOG].[PATIENT_ID]) AND ([EventArray].[Client_Event_Date]=[dbo_EVENTLOG].[EVENT_DATE])) LEFT JOIN Authorizations ON [dbo_EVENTLOG].[EVENT_ID]=[Authorizations].[Event_ID]) LEFT JOIN PatientAsPerson ON [EventArray].[Patient_ID]=[PatientAsPerson].[PATIENT_ID];

gravatar
Collapse -

report works, query doesn't

by nikki96 In reply to report works, query doesn ...

Is EventArray the name of a table?

FROM ((((((EventArray LEFT JOIN dbo_PROVIDER ON RefSchool=EXT_ID)

Are you sure neither RefSchool nor EXT_ID are variables? They are both fields in a table?

Are you sure the "IIF" construct works outside the report?

Can you create a new query in Access and copy/paste this? Does it work if you do?

gravatar
Collapse -

report works, query doesn't

by john_wills In reply to report works, query doesn ...

EventArray is the name of a table. RefSchool and EXT_ID are fields in tables - you would know which tables except that the comment field was not big enough to take them. I have used the IIF construct successfully outside reports. When I copy the SQLtext into a new query the new query works for a while but then starts saying "Invalid operation".

Back to Software Forum

Start or search

Related Discussions

Related Forums