Software

Ask something
Discussion Locked

report works, query doesn't

0 helpful
5 replies

by john_wills 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

5 replies

by nikki96

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.

by john_wills

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

by john_wills

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];

by nikki96

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?

by john_wills

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

This discussion is closed to new replies.