Criteria in Querry to include All and Empty Field

Thanks. I learned this one the hard way.


"John W. Vinson" wrote in message
...
On Sat, 27 Feb 2010 22:26:01 -0800, Tom Ventouris
wrote:

Thank you. I see, now, the significance of putting the criteria in the SQL
statement. It's all working.

"KenSheridan via AccessMonster.com" wrote:

This is why its much easier to write (and save) the query as SQL; you
just
tack each parenthesised OR operation together in a set of AND
operations:


Just one warning: if you carefully construct a beautiful such
multicriterion
query in SQL... and then open it in Query Design view... Access will turn
it
into a total mess. It may in fact cease to work correctly, and it will
totally
scramble your SQL (adding a calculated field for each criterion, etc.)
So...
create it in SQL and *NEVER* open it in the query design grid!
--

John W. Vinson [MVP]

ù*,jlmù

"Tom Ventouris" a écrit dans le
message de groupe de discussion :
...
Thank you. I see, now, the significance of putting the criteria in the SQL
statement. It's all working.

"KenSheridan via AccessMonster.com" wrote:

This is why its much easier to write (and save) the query as SQL; you
just
tack each parenthesised OR operation together in a set of AND operations:

SELECT *
FROM SomeTable
WHERE (SomeField = [SomeParameter]
OR [SomeParameter] IS NULL)
AND (SomeOtherField = [SomeOtherParameter]
OR [SomeOtherParameter] IS NULL)
AND (YetAnotherField = [YetAnotherParameter]
OR [YetAnotherParameter] IS NULL)
and so on;

Ken Sheridan
Stafford, England

Tom Ventouris wrote:
Thank you. This one seems to work too for one or two fields with
criteria,
however for some reason, as add more criteria in other fields, the
Criteria
lines in the query multiply. I cannot tell how many, but enough to cause
Access to crash.

Try this as the 'criteria' in query design view, all on one line:

[quoted text clipped - 35 lines]
source data tables?
Thanks in advance.

--
Message posted via http://www.accessmonster.com

.