Disable Access query rewriting

[StaZ]

Hello,
I would simply like to know if there's a way to disable the "feature"
of MS Access that rewrites your queries "correctly"...

This feature makes this : ...WHERE SomeBooleanField...;
Become : ...WHERE (((SomeBooleanField)<>false))...;

that's not so bad since the essential is kept in place... but here's
MY problem :

My query needs a LEFT JOIN using a Between Operator Like this :
FROM Students AS s LEFT JOIN Grades AS g ON (s.Average BETWEEN
g.LowestAverage AND g.HighestAverage)

Ms Access can handle such a join perfectly... but as long as you close
the Query or any object u wrote that in... the super Rewriting feature
comes and REVOMES THE PARENTHESIS AROUND THE BETWEEN... which makes
Access refuse to execute the query because of a missing operator....
which is really stupid!

So please if anybopdy know how to remove that rewriting feature from
my Access or how to write a LEFT JOIN using a BETWEEN operator in
another, please help me... i'm stuck!

Thank you very Much
StaZ

[Allen Browne]

Hi StaZ

I don't know if this will work, but it's worth a shot.
Try assigning the SQL property of the QueryDef directly.

Example:
Dim strSql As String
strSQL = "SELECT ...
CurrentDb().QueryDefs("Query1").SQL = strSQL

You cannot open the query in Design view anyway with this kind of query, and
I would expect Access to leave your query alone if it were forced to save
the SQL in this way, unless you did open it again form the interface.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"StaZ" <St*****@gmail.com> wrote in message
news:99**************************@posting.google.c om...

Hello,
I would simply like to know if there's a way to disable the "feature"
of MS Access that rewrites your queries "correctly"...

This feature makes this : ...WHERE SomeBooleanField...;
Become : ...WHERE (((SomeBooleanField)<>false))...;

that's not so bad since the essential is kept in place... but here's
MY problem :

My query needs a LEFT JOIN using a Between Operator Like this :
FROM Students AS s LEFT JOIN Grades AS g ON (s.Average BETWEEN
g.LowestAverage AND g.HighestAverage)

Ms Access can handle such a join perfectly... but as long as you close
the Query or any object u wrote that in... the super Rewriting feature
comes and REVOMES THE PARENTHESIS AROUND THE BETWEEN... which makes
Access refuse to execute the query because of a missing operator....
which is really stupid!

So please if anybopdy know how to remove that rewriting feature from
my Access or how to write a LEFT JOIN using a BETWEEN operator in
another, please help me... i'm stuck!

Thank you very Much
StaZ

[StaZ X]

Hi Mr. Browne

Thank you very much for your answer, it's a wonderful way to override
the "power" of access... i inserted this code u posted me in the
Report_Open event of my reports and it works wonderfully :)

thank you very much!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!