ARG! Stop changing my queries!

[madEG]

I am going crazy!

I have a form whose record set is an sql query that joins two tables by a where statement: "select this, that from here, there where here.id = there.id..." and it works just fine thank you.

Knowing that is what I set up, I also have some controls that will append to the record source's query "AND" statements that will further filter out records... sqltext = sqltext & " AND thisfield = " & etc etc...

BUT, I was getting errors and looked back to find that the crafted sql query is being modified to be an inner join sql statement... thereby causing syntax errors when I append the " AND this_field = " portion...

How can I disable Access from trying to improve my sql and just do what I ask it to?

Thoughts? Can I set the form's record source using VBA? I imagine that will remain safe from meddling...

Thanks!
-Matt G.

 

[madEG]

...also, it seems that this doesn't always happen - the changing that is - maybe only when I modify the query using the QBE?

Curious...

 

[Rabbie]

...also, it seems that this doesn't always happen - the changing that is - maybe only when I modify the query using the QBE?

Curious...

When you use the QBE Access has to convert what is on the grid into SQL. This is why your SQL is being "improved". if you work exclusively in SQL mode and don't use the design grid there shouldn't be any issues

 

[namliam]

Why use a "Where" when a Join is what you want??
The easy trick to make sure you can "AND" your where clause is to add a dummy where.
Where 1 = 1

Any and can be added to it.

 

[madEG]

Ok - I get it. I'll get back to it! Thanks for the tips guys...