MSA2k2 is REWRITING my query (and screwing it up)!

[Brainlord Mesomorpoh]

I've has MSA rewrite queries I've made before, sometimes I'll put "or" in a query and after I've saved it and opened it again Access has rearranged it so that the two criteria are now on two seperate rows on the QBE grid.

That's logical, that OK. What its doing now is not OK

I have a complex two-stage query, (if you want I'll post all the details, but I don't think we need them.) The 2nd query compares the 1st query (which may have inventory ID nos in any of 5 columns) to the Inventory Table. Now because the Inventory Numbers (InvID) may be in any of 5 columns, (Inv1, inv2, inv3, etc) I can't link the table and query directly together, to they are UNLINKED in this query. Then I filter the results, looking for InvIDs in Inv1, or Inv2 etc. This forms a nice little stairstep pattern in the QBE grid and most importantly IT WORKS

I test it, it works, I save it, it still works, I CLOSE it and open it again, IT STOPS WORKING. (actually it just returns EVERYTHING )

So I open it in Design View and MSA has changed things, In the criteria section of te QBE grid, my stairstep pattern of InvID has heen replaced with a stairstep pattern searching for Inv1! one of those fields its searching is Inv1 so it finds it every time. So I change all the criteria back to InvID, it works

I test it, it works, I save it, it still works, I CLOSE it and open it again, IT STOPS WORKING

Same thing! Now this isn't a minor correction of syntax, InvID and Inv1 are fields in two seperate tables! (ok a table and a query)

Why is access ruining my query?! (I've checked for database corruption, and new versions of Access) My project is at a stand-still.

Someone Please hel

AHA, TI
bl

 

[david epsom dot com dot au]

Rarely, the Jet query optimiser gets confused and mixes things up.
Primarily, I have seen problems with ODBC linked tables, Aggregate
Queries, and Cartesian Joins (unjoined tables).

Personally, I never query on unjoined tables. If necessary, I create
a dummy field in a subquery (One:1) so that I can join on the dummy
field rather than have an unjoined table.

I had assumed that this was just a stupid habit, based on an old bug
that was long since fixed, but possibly not.....

(david)

I've has MSA rewrite queries I've made before, sometimes I'll put "or" in

a query and after I've saved it and opened it again Access has rearranged it
so that the two criteria are now on two seperate rows on the QBE grid.

That's logical, that OK. What its doing now is not OK.

I have a complex two-stage query, (if you want I'll post all the details,

but I don't think we need them.) The 2nd query compares the 1st query (which
may have inventory ID nos in any of 5 columns) to the Inventory Table. Now
because the Inventory Numbers (InvID) may be in any of 5 columns, (Inv1,
inv2, inv3, etc) I can't link the table and query directly together, to they
are UNLINKED in this query. Then I filter the results, looking for InvIDs in
Inv1, or Inv2 etc. This forms a nice little stairstep pattern in the QBE
grid and most importantly IT WORKS!

I test it, it works, I save it, it still works, I CLOSE it and open it

again, IT STOPS WORKING. (actually it just returns EVERYTHING )

So I open it in Design View and MSA has changed things, In the criteria

section of te QBE grid, my stairstep pattern of InvID has heen replaced with
a stairstep pattern searching for Inv1! one of those fields its searching is
Inv1 so it finds it every time. So I change all the criteria back to InvID,
it works.

I test it, it works, I save it, it still works, I CLOSE it and open it again, IT STOPS WORKING.

Same thing! Now this isn't a minor correction of syntax, InvID and Inv1

are fields in two seperate tables! (ok a table and a query)

Why is access ruining my query?! (I've checked for database corruption,

and new versions of Access) My project is at a stand-still.