How to keep Access from rearranging logical expressions iin the criteria cells ?

[pschrader]

Hi !

I'm using MS Access 2003.

In my query, in lots of yes/no columns, I enter criteria.
In my case expressions like

(=Forms!frmCriteria!checkboxN) OR (Forms!frmCriteria!checkboxN=False)

After editing in design mode and saving the query, I close the design
window.
The query works fine.
I open it again in design mode, and ...
all my logical expressions have been transformed into a logically equivalent
arrangement.
They spread over more cells.
Everything looks very different.
And I do not like to take this state as a basis for further changes.
Because I feel that my way of expressing the logic is much clearer than MS
Access's way.
Is there a way to keep Access from rearranging these logical expressions in
the criteria cells?

Thank you very much for your kind help !

Peter

 

[Guest]

I feel your pain. The only thing I can think of is creating SQL strings in
functions and running them there. Of course the query might not get optimized
like a stored query and there could be a performance hit. It would also be a
little difficult to use as a record source for a form or report.

 

['69 Camaro]

Hi, Peter.

Is there a way to keep Access from rearranging these logical expressions
in the criteria cells?

Yes. Don't open the query in Design View for the QBE grid. Switch to SQL
View and save it in SQL View, and the next time you open the query, it will
automatically open the SQL View pane, not the QBE grid, showing your
expressions they way you wrote them.

The caveat is that you'll need to learn SQL, but SQL is much more powerful
than the QBE grid for creating and editing queries. However, if you must
switch to the QBE grid for something, first copy the SQL statement to
Notepad first, then switch to the QBE grid. When you switch back to SQL
View, if Jet has "optimized" your query by rearranging your expressions,
then you can replace the SQL with the SQL statement you saved in Notepad.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[John W. Vinson]

Is there a way to keep Access from rearranging these logical expressions in
the criteria cells?

Only by creating the query in SQL view, and never opening it in design view.

John W. Vinson [MVP]

 

[Guest]

Dont use the query builder. I've worked with Access for over 7 years and have
never used it yet.

-Dorian