Re: Can someone explain the behavior of this query?

My first "guess" is this function sometimes fails:

Left([qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],InStr(1,[qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],Chr(124))-1)


If Chr(124) is not found in the field's string value, the Left function will
fail.

--

Ken Snell
<MS ACCESS MVP>


<cputnam@xxxxxxx> wrote in message
news:1186595161.643420.295230@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have a query that sometimes gives me the "Invalid Operation" error
message but sometimes it doesn't. This query contains LOTS of Right
Joins (because I need to return records that may not have ALL the data
entered into them already). It uses ODBC pass through queries because
the original database is a Sybase database.

The Key field for all the queries/tables that are used in this query
is the PBR_IFMS_ID field. If I enter an actual PBR_IFMS_ID as a
criteria in that field, I get the Invalid Operation error. BUT if I
enter all or part of the ProjectName (another important piece of
information), the query runs fine. If I prompt the user for all or
part of the ProjectName, the query runs fine too. I read that
parameterized queries couldn't be used with ODBC queries so I thought
this information was important.

We want to be able to use the actual PBR_IFMS_ID as criteria because
the query SHOULD RUN FASTER that way (and it's slow enough as it is).

BTW -- I created this query slowly, adding one subquery at a time. I
didn't get the "Invalid Operation" error until I added the
qsel_ProjectAbstractSumOthExpenses_CP0101 subquery. BUT I don't have
any trouble running that query by itself.

I am using Windows XP version 2002 SP2 and Access 2002 SP3. Thanks in
advance for any help you can give.

Carol.
-----------------------------------------------------
Here is the SQL that I am using (sorry it is so complicated)

SELECT qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID,
qspt_Pbrupdate_CLSSYSVI.PROJUPDATE_IFMS_ID,
qspt_Pbrupdate_CLSSYSVI.ProjectName,
qspt_Pbrupdate_CLSSYSVI.FundsRaised, qspt_Pbrupdate_CLSSYSVI.Measures,
qspt_Pbrupdate_CLSSYSVI.MonitoringPlanImplemented,
qspt_Pbrupdate_CLSSYSVI.StaffingInPlace, qspt_Pbrupdate_CLSSYSVI.URLs,
qspt_Pbrupdate_CLSSYSVI.ConsMonStewMeasures,
qspt_Pbrupdate_CLSSYSVI.ProjectRating,
qspt_Pbrupdate_CLSSYSVI.BOTReviewDate AS BOTReview,
qspt_Pbr_State_Key_State_CLSSYSVI.StateCode,
qspt_Nation_CLSSYSVI.NationName, IIf(([StateName]<>"XX" And
[NationName]="United States"),[StateName],[StateName] & ", " &
[NationName]) AS Intl, IIf([qspt_PBRUpdate_clssysvi]![ApprovalStatus]
Is Null,Null,IIf(InStr(1,[qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],Chr(124))=0,[qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],Left([qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],InStr(1,[qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],Chr(124))-1))) AS AppStatus,
qsel_ProjectAbstractPBR_USVAWO01.Authority,
qsel_ProjectAbstractKeyPartners_USCP0101.KeyPartnerList,
qsel_ProjectAbstractSumLoan_USVAWO01.Int,
qsel_ProjectAbstractSumsRESE_USCP0101.ResSum,
qsel_ProjectAbstractSumsRESE_USCP0101.StewStartSum,
qsel_ProjectAbstractSumOthExpenses_USCP0101.SumOfOtherExpenses
FROM (((((qspt_Pbrupdate_CLSSYSVI INNER JOIN
qsel_ProjectAbstractPBR_USVAWO01 ON
qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID =
qsel_ProjectAbstractPBR_USVAWO01.PBR_IFMS_ID) LEFT JOIN
(qspt_Pbr_State_Key_State_CLSSYSVI LEFT JOIN qspt_Nation_CLSSYSVI ON
qspt_Pbr_State_Key_State_CLSSYSVI.NATION_IFMS_ID =
qspt_Nation_CLSSYSVI.NATION_IFMS_ID) ON
qsel_ProjectAbstractPBR_USVAWO01.PBR_IFMS_ID =
qspt_Pbr_State_Key_State_CLSSYSVI.PBR_IFMS_ID) LEFT JOIN
qsel_ProjectAbstractKeyPartners_USCP0101 ON
qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID =
qsel_ProjectAbstractKeyPartners_USCP0101.PBR_IFMS_ID) LEFT JOIN
qsel_ProjectAbstractSumLoan_USVAWO01 ON
qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID =
qsel_ProjectAbstractSumLoan_USVAWO01.PBR_IFMS_ID) LEFT JOIN
qsel_ProjectAbstractSumsRESE_USCP0101 ON
qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID =
qsel_ProjectAbstractSumsRESE_USCP0101.PBR_IFMS_ID) LEFT JOIN
qsel_ProjectAbstractSumOthExpenses_USCP0101 ON
qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID =
qsel_ProjectAbstractSumOthExpenses_USCP0101.PBR_IFMS_ID
WHERE (((qspt_Pbrupdate_CLSSYSVI.PBR_IFMS_ID)="2012062779802550"))
ORDER BY qspt_Pbrupdate_CLSSYSVI.ProjectName;



.

Relevant Pages

  • Re: Error 3420 Object invalid or no longer set under Vista
    ... Sometimes the JET optimizer does not run the subquery to completion. ... Problem is in the ORDER BY statement of the problem query below ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Error 3420 Object invalid or no longer set under Vista
    ... optimizer does not run the subquery to completion. ... Database is local, nothing across a network. ... Problem is in the ORDER BY statement of the problem query ... draws from 2 other queries, the problem could be lower down. ...
    (microsoft.public.access.queries)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
Quantcast