Re: Can someone explain the behavior of this query?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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>


<cputnam@xxxxxxx> wrote in message
news:1186605485.643329.165980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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\)"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> wrote:
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>

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




.

Relevant Pages

  • Re: Can someone explain the behavior of this query?
    ... from the query. ... use part of the ProjectName as criteria. ... It uses ODBC pass through queries because ...
    (microsoft.public.access.queries)
  • Re: Can someone explain the behavior of this query?
    ... Your query is fairly long, so it's not easy to identify where you are making ... Post the SQL statements of the queries using both approaches so ... Put the exact PBR_IFMS_ID as criteria into the ... as criteria into the ProjectName column. ...
    (microsoft.public.access.queries)
  • Re: Can someone explain the behavior of this query?
    ... This query contains LOTS of Right ... It uses ODBC pass through queries because ... I get the Invalid Operation error. ... part of the ProjectName, ...
    (microsoft.public.access.queries)
  • Re: Sorry... here you have.
    ... results as your original query, ... You'll have to change vwSaldos to read from ... we need the account ID in the results ...
    (microsoft.public.sqlserver.programming)
  • Re: Can someone explain the behavior of this query?
    ... I have recreated the query a bit ... Post the SQL statements of the queries using both approaches so ... Put the exact PBR_IFMS_ID as criteria into the ... as criteria into the ProjectName column. ...
    (microsoft.public.access.queries)