Criteria in Querry to include All and Empty Field

[Tom Ventouris]

I run a query from a form where the criteria are selected from a combo box.
The combo box is based obn a Union Query to include <<ALL>>.

The crieria I set in myField in the query:
Like
(IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>","*",[Forms]![frmMyForm]![MyComboBox]))
How can I get the query to include records where myField is blank in the
source data tables?
Thanks in advance.

 

[Allen Browne]

It is possible to massage the WHERE clause of the query so that it evaluates
to True for all records if the combo's value is "<<ALL>>".

Switch the query to SQL View.
Locate the WHERE clause.
Set it up like this:
WHERE IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>", True,
[SomeField] = [Forms]![frmMyForm]![MyComboBox])

This gets tiresome and clumsy where you have lots of fields to filter on, so
it's probably better to learn how to do it in the filter of the form rather
than directly in the query. Here's an example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Tom Ventouris]

Thank you.
I have tried changing the query criteria, however:
1. When I select a value in myComboBox, the query does not return any
records. (They are there)
2. When I select <<ALL>> in myComboBox, I get " the query is too complex to
be evaluated...."

It is possible to massage the WHERE clause of the query so that it
evaluates to True for all records if the combo's value is "<<ALL>>".

Switch the query to SQL View.
Locate the WHERE clause.
Set it up like this:
WHERE IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>", True,
[SomeField] = [Forms]![frmMyForm]![MyComboBox])

This gets tiresome and clumsy where you have lots of fields to filter on,
so it's probably better to learn how to do it in the filter of the form
rather than directly in the query. Here's an example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I run a query from a form where the criteria are selected from a combo
box. The combo box is based obn a Union Query to include <<ALL>>.

The crieria I set in myField in the query:
Like
(IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>","*",[Forms]![frmMyForm]![MyComboBox]))
How can I get the query to include records where myField is blank in the
source data tables?
Thanks in advance.

 

[Allen Browne]

Did you try it in SQL View?

(Or did you merely type this into the Criteria row in design view?)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you.
I have tried changing the query criteria, however:
1. When I select a value in myComboBox, the query does not return any
records. (They are there)
2. When I select <<ALL>> in myComboBox, I get " the query is too complex
to be evaluated...."

It is possible to massage the WHERE clause of the query so that it
evaluates to True for all records if the combo's value is "<<ALL>>".

Switch the query to SQL View.
Locate the WHERE clause.
Set it up like this:
WHERE IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>", True,
[SomeField] = [Forms]![frmMyForm]![MyComboBox])

This gets tiresome and clumsy where you have lots of fields to filter on,
so it's probably better to learn how to do it in the filter of the form
rather than directly in the query. Here's an example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I run a query from a form where the criteria are selected from a combo
box. The combo box is based obn a Union Query to include <<ALL>>.

The crieria I set in myField in the query:
Like
(IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>","*",[Forms]![frmMyForm]![MyComboBox]))
How can I get the query to include records where myField is blank in the
source data tables?
Thanks in advance.

 

[Tom Ventouris]

Thanks again.
I did not try it in SQL view, apologies, the first instruction was clear.
I have now tried it in SQL View and works.

I see that the query criteria appear as Expr1 in Design View. I have created
additional criteria and they appear as Expr2 through to Expr5.
This works exactly as expected.

Thank you again for coming back after my second posting.

Did you try it in SQL View?

(Or did you merely type this into the Criteria row in design view?)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you.
I have tried changing the query criteria, however:
1. When I select a value in myComboBox, the query does not return any
records. (They are there)
2. When I select <<ALL>> in myComboBox, I get " the query is too complex
to be evaluated...."

It is possible to massage the WHERE clause of the query so that it
evaluates to True for all records if the combo's value is "<<ALL>>".

Switch the query to SQL View.
Locate the WHERE clause.
Set it up like this:
WHERE IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>", True,
[SomeField] = [Forms]![frmMyForm]![MyComboBox])

This gets tiresome and clumsy where you have lots of fields to filter
on, so it's probably better to learn how to do it in the filter of the
form rather than directly in the query. Here's an example:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


I run a query from a form where the criteria are selected from a combo
box. The combo box is based obn a Union Query to include <<ALL>>.

The crieria I set in myField in the query:
Like
(IIf([Forms]![frmMyForm]![MyComboBox]="<<ALL>>","*",[Forms]![frmMyForm]![MyComboBox]))
How can I get the query to include records where myField is blank in
the source data tables?
Thanks in advance.

 

[Tom Ventouris]

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:

[Forms]![frmMyForm]![MyComboBox] Or [Forms]![frmMyForm]![MyComboBox]
="<<ALL>>"

In SQL view:

WHERE ([SomeField] = [Forms]![frmMyForm]![MyComboBox]
OR [Forms]![frmMyForm]![MyComboBox]="<<ALL>>")

If the value in the field equals the selected value in the combo box then the
first part of the OR operation in the expression will evaluate to True for
that row, so the row will be returned; if <<ALL>> is selected in the combo
box then the other part of the OR operation in the expression will evaluate
to True regardless of the value (or absence of a value) in the field, so
every row will be returned.

Note that if you set this up in query design view, and save the query, when
you open it again in design view Access will have moved things around. The
logic will be the same, however, and it will work just the same. If using
multiple 'optionalized' parameters by tacking a series of OR operations
together with ANDs. the logic is far clearer in SQL, so it makes sense to
save the query in SQL view if later amendments are contemplated.

Ken Sheridan
Stafford, England

Thank you.
I have tried changing the query criteria, however:
1. When I select a value in myComboBox, the query does not return any
records. (They are there)

It is possible to massage the WHERE clause of the query so that it
evaluates to True for all records if the combo's value is "<<ALL>>".

[quoted text clipped - 21 lines]

source data tables?
Thanks in advance.

--



.

 

[Tom Ventouris]

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

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

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.

 

[John W. Vinson]

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

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!

 

[Tom Ventouris]

Thanks. I learned this one the hard way.

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!

 

[joelgeraldine]

ù*,jlmù

"Tom Ventouris" <[email protected]> a écrit dans le
message de groupe de discussion :
(e-mail address removed)...

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

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

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.