Forms!myForm!myControl OR Forms!myForm!myControl Is Null

[Veron]

Found a link for building search criteria: http://www.mvps.org/access/queries/qry0001.htm

[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null

Thought it would be easy.


In the ContactTitle and City columns, I put in the criteria:
forms!frmSearch!txtTitle or forms!frmSearch!txtTitle Is Null
forms!frmSearch!txtCity or forms!frmSearch!txtCity Is Null

But after the query is saved, Access changed the two columns into four columns and the criteria spanned four rows. (Please see jpg attached)


The SQL statement is difficult to read, with all the ANDs and ORs and brackets:
SELECT Clients.ContactTitle, Clients.City
FROM Clients
WHERE (((Clients.ContactTitle)=[forms]![frmSearch]![txtTitle]) AND ((Clients.City)=[forms]![frmSearch]![txtCity])) OR (((Clients.City)=[forms]![frmSearch]![txtCity]) AND (([forms]![frmSearch]![txtTitle]) Is Null)) OR (((Clients.ContactTitle)=[forms]![frmSearch]![txtTitle]) AND (([forms]![frmSearch]![txtCity]) Is Null)) OR ((([forms]![frmSearch]![txtTitle]) Is Null) AND (([forms]![frmSearch]![txtCity]) Is Null));


My questions are:
Why did Access change the criteria?
How do I add additional criteria after the query is saved?

Any help is greatly appreciated.

 

[WayneRyan]

Veron,

1) I hate what Access does to the human-readable representation of
a query! It adds a lot of unwanted things, (at times), like too
many parenthesis and table prefixes. To me, a query can best be
portrayed as a nicely formatted SQL string:

Select A.HoursWorked,
       B.LastName,
       B.FirstName
From   tblTimeCards As A Left Join tblEmployee As B On
       A.EmpID = B.EmpID
Where  A.HoursWorked > 8

As queries get more complex, I find that the Query Grid and the
SQL View "linear text string" are too difficult. You can always
maintain/develop your SQL scripts and past them into the SQL view.

2) In the Query Grid, the following are the same:

a)                   Column 1
                     ================================================
     FieldName --->  [ContactTitle]
     Criteria  --->  = "Boss" Or Forms![SomeForm]![SomeField] Is Null

and:

b)                   Column 1         Column 2 (Access Added)
                     ===============  ===============================
     FieldName --->  [ContactTitle]   Expr1:  Forms![SomeForm]![SomeField] Is Null
     Criteria  --->  = "Boss"         True

Both of the above are logically the same. Personally, I prefer neither.
If I had to maintain that as the logic evolved, I'd work with a nicely
formatted SQL statement anyday.

Wayne

 

[Keith27]

You can find the answers to your two questions in Jon K's post in this thread
http://www.access-programmers.co.uk/forums/showthread.php?t=87462


According to him, instead of using:

[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null​

as the criteria for a field, you can start a new column and put:
------------------------------------
Field: [FieldName]=[Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null

Show: uncheck

Criteria: True
------------------------------------

Access will leave everything unchanged when the query is saved.

 

[Veron]

Thank you very much. That worked.