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


<cputnam@xxxxxxx> wrote in message
news:1186610833.649523.290980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


On Aug 8, 4:46 pm, "Ken Snell \(MVP\)"
<kthsneisll...@xxxxxxxxxxxxxxxxxx> wrote:
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>

<cput...@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.- Hide quoted text -

- Show quoted text -




.

Relevant Pages

  • RE: link pull down/drop down menu to query access
    ... tab on the form in the query criteria. ... one alternative is to use 5 different queries. ... Dim varFiscalWeek As Variant ...
    (microsoft.public.access.forms)
  • RE: link pull down/drop down menu to query access
    ... tab on the form in the query criteria. ... one alternative is to use 5 different queries. ... Dim varFiscalWeek As Variant ...
    (microsoft.public.access.forms)
  • Re: multiple variables
    ... stuff in a query's criteria is just making the problem more ... could reduce the number of queries that you need for all ... regular query). ... DoCmd.OpenReport "report name", acViewPreview, _ ...
    (microsoft.public.access.formscoding)
  • RE: link pull down/drop down menu to query access
    ... one alternative is to use 5 different queries. ... Now, in your query, put this in the Criteria row of the field that stores ... Dim varFiscalWeek As Variant ...
    (microsoft.public.access.forms)
  • Re: Access: Add "Row Source Lookup" to "Query Parameters"
    ... Hello, Tom. ... I understand how form values can be retreived for queries. ... parameters with by possible without having to create controls criteria forms. ... The query can automatically generate the criteria input form based on the ...
    (microsoft.public.access.queries)