How to stop Access re-arranging query criteria onto multiple criteria lines for OR condition

[Chrisso]

Hi All

I have a form(cFrmDashboard) with text boxes for the user to enter
criteria and a list box which displays a query that uses the values
entered in the text boxes to search my main table.

In my search query for each field that I have provided a text box I
enter the following criteria (for example":

Like "*" & Forms![cFrmDashboard]![txtType] & "*" Or Forms!
[cFrmDashboard]![txtType] Is Null

I enter these all on the 1st "Criteria" line and this is easy to read
and change.

This works fine until I want to add another field to my search query.
When I reopen the query Access has decided to rewrite my query by
splitting up all my statements onto seperate lines to deal with the OR
condition.

I would not mind this so much but it makes it impossible to maintain
the query and sometimes once this happen the query just does not work
anymore.

Is there a way of telling Access not to reinterpret my criteria for my
query?

Has anyone else had this problem and how do you deal with it to make
sure that you can update/maintain such a query?

Is this the best way/practise to provide a user driven search form?

Thanks for any ideas,
Chrisso

 

[Guest]

There are a couple of options that I know.

The first is to always, I mean always, open up the query in SQL view. Once
you save a query while in SQL view, it should open it that way again. If you
go over to the QBE grid, Access has a chance to "optimize" it.

The second it to write your queries in modules. Something like the following:

DoCmd.RunSQL "SELECT MSysObjects.* INTO tblMakeEmptyTable FROM MSysObjects
WHERE 1 =2;"

Or something like this in a module. It only works on Action queries and not
Select.

Dim strSQLChange As String

strSQLChange = "UPDATE Employees SET Country = " & _
"'United States' WHERE Country = 'USA'"

dbsNorthwind.Execute strSQLChange, dbFailOnError

 

[John W. Vinson]

Is there a way of telling Access not to reinterpret my criteria for my
query?

Create the query in SQL view; save it; and never, ever switch to the query
design window.

Is this the best way/practise to provide a user driven search form?

Well... for two or three criteria, maybe, though I would NOT use the LIKE
operator since it can give unexpected results. Searching for a LastName of Li
will find the record for Mr. Gigliardi, which you might not want!

For more than a very few criteria, you're really better off writing VBA code
to construct a SQL WHERE clause and actually build the SQL string on the fly;
then assign it to a form or report's Recordsource. Simply skip any controls
with null values.

John W. Vinson [MVP]

 

[Chrisso]

Thanks guys for your post - I was afraid that there would be no nice
solution.

I can deal with keeping the query in SQL and modding it directly but I
have to hand this system over on completeion to maintenance people who
can only deal with Query Builder queries as they are not actual
programmers and have no SQL experience.

Hmmmmmm - I might have to have to provide really tightly documented
process to them for extending the SQL but I am not hopeful. I am
surprised that there is not a nicer way to create and maintain such a
query which nearly every system would need!

Anyone else out there got any thoughts?

Chrisso