Criteria for Query IIF clause

  • Thread starter Thread starter BurtArkin
  • Start date Start date
B

BurtArkin

I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If I
leave the dropdown box null, blank and empty, I get strange and inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks
 
Hi Burt,

What is the assignment of ">1" meant to do and what result would you expect?

Kevin
 
The >1 refers to the auto id numbers in the SCTypes table. If I select a
type in the drop down box, it works fine as a filter. It's when I leave the
drop down box null that I want all records, unfiltered, so I ask for all
records with an auto id greater than 1.
--
Burt


KC-Mass said:
Hi Burt,

What is the assignment of ">1" meant to do and what result would you expect?

Kevin


BurtArkin said:
I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm
using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If
I
leave the dropdown box null, blank and empty, I get strange and
inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks
Click to expand...
Click to expand...
 
The >1 refers to the auto id numbers in the SCTypes table. If I select a
type in the drop down box, it works fine as a filter. It's when I leave the
drop down box null that I want all records, unfiltered, so I ask for all
records with an auto id greater than 1.
--
Burt


KC-Mass said:
Hi Burt,

What is the assignment of ">1" meant to do and what result would you expect?

Kevin


BurtArkin said:
I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm
using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If
I
leave the dropdown box null, blank and empty, I get strange and
inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks
Click to expand...
Click to expand...
 
IIF statements need to have IIF([field]>1,[what to do if true],[what to do if
false])
So for you, you need to fil in the false part AND there is no comma after
field.
 
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])

The source drop down box is null, which is the condition. What is not
working is the second part, where I want all the records unfiltered.

Thanks
--
Burt


golfinray said:
IIF statements need to have IIF([field]>1,[what to do if true],[what to do if
false])
So for you, you need to fil in the false part AND there is no comma after
field.
--
Milton Purdy
ACCESS
State of Arkansas


BurtArkin said:
I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If I
leave the dropdown box null, blank and empty, I get strange and inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks
Click to expand...
Click to expand...
 
You cannot change a criteria from = to > like that.

Instead, set your criteria to

[Forms]![ReportForm]![cboSCType] OR ([Forms]![ReportForm]![cboSCType] IS
NULL)
 
I'm confused. I thought you could have a query with an iif clause on the
criteria line. That's why I set it to:

IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])

where the criterion for a null drop down box is an unfiltered table, or
filtered by the inut in the drop down box. The aim is to get either a
filtered batch of data, or all the data based on the selection or lack
thereof.

If that's fundamentally incorrect--that you cannot have an iif statement on
the criteria line--how do you enable the desired choice?

Thanks again for your response.
--
Burt


Douglas J. Steele said:
You cannot change a criteria from = to > like that.

Instead, set your criteria to

[Forms]![ReportForm]![cboSCType] OR ([Forms]![ReportForm]![cboSCType] IS
NULL)
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


BurtArkin said:
I have a query where I want to show either a single selection from a drop
down box, or all records if the dropdown box is null. The language I'm
using
is:
IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])
When a selection is made in the cboSCType dropdown, everything works. If
I
leave the dropdown box null, blank and empty, I get strange and
inconsistent
results. (I'm using Access 2002 on an XP Pro system. The elements of the
dropdown box are autonumbered.)
Please tell me what I'm doing wrong. Thanks
Click to expand...
Click to expand...
 
You use the method that Mr. Steele suggested. Enter the following as the
criteria under your field.

[Forms]![ReportForm]![cboSCType] OR [Forms]![ReportForm]![cboSCType] IS NULL

This will filter by the value in cboSCType unless the value is null. If the
value is null in cboSCType, then the second part of the expression is true and
ALL records will be returned based on this field. If you have other criteria,
that will still apply.

The secret is that a record is returned if the expression evaluates to true
and it will evaluate to true if either expression is true.

So if
somefield = [Forms]![ReportForm]![cboSCType], that row will be returned
because of the true generated by the expression. OR if
[Forms]![ReportForm]![cboSCType] is null, then you will get TRUE and the row
is returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Sorry about that!! I misread Mr Steele's suggestion. Thank you both for all
your help. I really appreciate you folks "being there" for people like me.
--
Burt


John Spencer MVP said:
You use the method that Mr. Steele suggested. Enter the following as the
criteria under your field.

[Forms]![ReportForm]![cboSCType] OR [Forms]![ReportForm]![cboSCType] IS NULL

This will filter by the value in cboSCType unless the value is null. If the
value is null in cboSCType, then the second part of the expression is true and
ALL records will be returned based on this field. If you have other criteria,
that will still apply.

The secret is that a record is returned if the expression evaluates to true
and it will evaluate to true if either expression is true.

So if
somefield = [Forms]![ReportForm]![cboSCType], that row will be returned
because of the true generated by the expression. OR if
[Forms]![ReportForm]![cboSCType] is null, then you will get TRUE and the row
is returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm confused. I thought you could have a query with an iif clause on the
criteria line. That's why I set it to:

IIf(IsNull([Forms]![ReportForm]![cboSCType]),>1,[Forms]![ReportForm]![cboSCType])

where the criterion for a null drop down box is an unfiltered table, or
filtered by the inut in the drop down box. The aim is to get either a
filtered batch of data, or all the data based on the selection or lack
thereof.

If that's fundamentally incorrect--that you cannot have an iif statement on
the criteria line--how do you enable the desired choice?

Thanks again for your response.
Click to expand...
Click to expand...
 
Back
Top