Can someone explain the behavior of this query?

C

cputnam

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

Ken Snell \(MVP\)

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>


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;
Click to expand...
 
C

cputnam

Thanks, Ken, that makes some sense but why, when I was running the
query on the same project (either by using the PBR_IFMS_ID or "like
*john moore*" as criteria) would it sometimes fail and sometimes
work?

Thanks.

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>




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.
Click to expand...
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.
Click to expand...
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).
Click to expand...
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.
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

I don't see any Like statement in your original query, so I'm at a loss for
giving a suggestion. Can you be more specific about which part of the query
you're referencing?

--

Ken Snell
<MS ACCESS MVP>


Thanks, Ken, that makes some sense but why, when I was running the
query on the same project (either by using the PBR_IFMS_ID or "like
*john moore*" as criteria) would it sometimes fail and sometimes
work?

Thanks.

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>




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.
Click to expand...
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.
Click to expand...
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).
Click to expand...
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.
Click to expand...
Click to expand...
Click to expand...
 
C

cputnam

Sorry that I wasn't more clear. I have two possibilities for running
the query: 1. Put the exact PBR_IFMS_ID as criteria into the
PBR_IFMS_ID column. and 2. Putting all or part of the "project name"
as criteria into the ProjectName column. I've had situations where
one small mistake in typing causes a query not to work so I usually
use "Like" for that type of criteria. For example, the whole project
name is "Hickory Nut Gorge (John Moore)" so if I just put in like
*john moore* that should pull up the correct record.

Carol.


I don't see any Like statement in your original query, so I'm at a loss for
giving a suggestion. Can you be more specific about which part of the query
you're referencing?

--

Ken Snell
<MS ACCESS MVP>




Thanks, Ken, that makes some sense but why, when I was running the
query on the same project (either by using the PBR_IFMS_ID or "like
*john moore*" as criteria) would it sometimes fail and sometimes
work?
Thanks.
Click to expand...
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>

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.- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
C

cputnam

I looked at the IIF and LEFT statements that Ken was referring to but
it looked like they were created correctly. So I removed that column
from the query. BUT still got the "Invalid operation" message when I
used the PBR_IFMS_ID as criteria. Tried it with another PBR_IFMS_ID
and had the same problem. But didn't get the error when I tried to
use part of the ProjectName as criteria.

ugh.

Carol.


Thanks, Ken, that makes some sense but why, when I was running the
query on the same project (either by using the PBR_IFMS_ID or "like
*john moore*" as criteria) would it sometimes fail and sometimes
work?

Thanks.

My first "guess" is this function sometimes fails:
Left([qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],InStr(1,[qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],Chr(124))-1)
Click to expand...

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

Ken Snell
<MS ACCESS MVP>
Click to expand...

- Show quoted text -
Click to expand...
 
K

Ken Snell \(MVP\)

Your query is fairly long, so it's not easy to identify where you are making
this change. Post the SQL statements of the queries using both approaches so
that we can see what you're doing.

--

Ken Snell
<MS ACCESS MVP>


Sorry that I wasn't more clear. I have two possibilities for running
the query: 1. Put the exact PBR_IFMS_ID as criteria into the
PBR_IFMS_ID column. and 2. Putting all or part of the "project name"
as criteria into the ProjectName column. I've had situations where
one small mistake in typing causes a query not to work so I usually
use "Like" for that type of criteria. For example, the whole project
name is "Hickory Nut Gorge (John Moore)" so if I just put in like
*john moore* that should pull up the correct record.

Carol.


I don't see any Like statement in your original query, so I'm at a loss
for
giving a suggestion. Can you be more specific about which part of the
query
you're referencing?

--

Ken Snell
<MS ACCESS MVP>




Thanks, Ken, that makes some sense but why, when I was running the
query on the same project (either by using the PBR_IFMS_ID or "like
*john moore*" as criteria) would it sometimes fail and sometimes
work?
Thanks.
Click to expand...

On Aug 8, 2:16 pm, "Ken Snell \(MVP\)"
My first "guess" is this function sometimes fails:
Left([qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],InStr(1,[qspt_PBRUpdate_clssysvi]!
[ApprovalStatus],Chr(124))-1)
Click to expand...

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

Ken Snell
<MS ACCESS MVP>
Click to expand...
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.
Click to expand...
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.
Click to expand...
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).
Click to expand...
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.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
Click to expand...
 
C

cputnam

Thanks for your help, Ken. I have recreated the query a bit
differently and it seems to be working fine now. Cross your fingers
for me.

Carol.


Your query is fairly long, so it's not easy to identify where you are making
this change. Post the SQL statements of the queries using both approaches so
that we can see what you're doing.

--

Ken Snell
<MS ACCESS MVP>




Sorry that I wasn't more clear. I have two possibilities for running
the query: 1. Put the exact PBR_IFMS_ID as criteria into the
PBR_IFMS_ID column. and 2. Putting all or part of the "project name"
as criteria into the ProjectName column. I've had situations where
one small mistake in typing causes a query not to work so I usually
use "Like" for that type of criteria. For example, the whole project
name is "Hickory Nut Gorge (John Moore)" so if I just put in like
*john moore* that should pull up the correct record.
Carol.
Click to expand...
I don't see any Like statement in your original query, so I'm at a loss
for
giving a suggestion. Can you be more specific about which part of the
query
you're referencing?
--
Ken Snell
<MS ACCESS MVP>

Thanks, Ken, that makes some sense but why, when I was running the
query on the same project (either by using the PBR_IFMS_ID or "like
*john moore*" as criteria) would it sometimes fail and sometimes
work?
Thanks.
On Aug 8, 2:16 pm, "Ken Snell \(MVP\)"
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>

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.- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...

- Show quoted text -
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

can someone take a look at this query for me? thanks. 1
Peculiar Behavior of Query 2
The Many Flavors of SQL - Can a SQL Server query work in MS Access? 7
How can 8 fields be too many in a query?? 1
Want query to output a word instead of the field 3
Right format of dates for query criteria 1
How can I make the following query read-write rather than read-only? 1

Top